למד כיצד לשלב מקורות נתונים מרובים (Power Query)

חל על
Excel של Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

בערכת לימוד זו, באפשרותך להשתמש Power Query של עורך Power Query כדי לייבא נתונים מקובץ Excel מקומי המכיל פרטי מוצר ומהזנת OData המכילה מידע על הזמנות מוצרים. אתה מבצע שלבי שינוי וצבירה, ומשלב נתונים משני המקורות כדי ליצור דוח "סה"כ מכירות למוצר ושנה".   

כדי לבצע ערכת לימוד זו, דרושה לך חוברת העבודה Products . בתיבת הדו-שיח שמירה בשם, תן לקובץ את השם Products and Orders.xlsx.

משימה 1: ייבוא מוצרים לחוברת עבודה של Excel

במשימה זו, תייבא מוצרים מהקובץ Products ו- Orders.xlsx (שהורדת ושנו את שמם שלעיל) לחוברת עבודה של Excel, תקדם שורות לכותרות העמודות, תסיר כמה עמודות ותטען את השאילתה לגליון עבודה.

שלב 1: התחברות לחוברת עבודה של Excel

  1. צור חוברת עבודה של Excel.
  2. בחר נתונים קבל>נתונים מקובץ>מתוך>חוברת עבודה.
  3. בתיבת הדו-שיח ייבוא נתונים, אתר את Products.xlsx הקובץ שהורדת ולאחר מכן בחר פתח.
  4. בחלונית נווט , לחץ פעמיים על הטבלה Products . הטבלה עורך Power Query מופיעה.

שלב 2: בחינת שלבי השאילתה

כברירת מחדל, Power Query מוסיף באופן אוטומטי כמה שלבים לנוחיותך. בדוק כל שלב תחת שלבים שהוחלו בחלונית הגדרות שאילתה לקבלת מידע נוסף.

  1. לחץ באמצעות לחצן העכבר הימני על השלב מקור ובחר ערוך הגדרות. שלב זה נוצר בעת ייבוא חוברת העבודה.
  2. לחץ באמצעות לחצן העכבר הימני על שלב הניווט ובחר ערוך הגדרות. שלב זה נוצר כאשר בחרת את הטבלה מתיבת הדו-שיח ניווט.
  3. לחץ באמצעות לחצן העכבר הימני על השלב סוג שהשתנה ובחר ערוך הגדרות. שלב זה נוצר על-ידי Power Query אשר הסיק את סוגי הנתונים של כל עמודה. בחר את החץ למטה משמאל ל שורת הנוסחאות כדי לראות את הנוסחה המלאה.

שלב 3: הסרת עמודות אחרות כדי להציג רק עמודות מעניינות

בשלב זה תסיר את כל העמודות למעט ProductID,‏ ProductName,‏ CategoryID ו- QuantityPerUnit.

  1. בתצוגה מקדימה של נתונים, בחר את העמודות ProductID, ProductName, CategoryID ו- QuantityPerUnit (השתמש ב- Ctrl+לחיצה או Shift+לחיצה).
  2. בחר הסר עמודות הסר>עמודות אחרות.
    הסתרת עמודות אחרות

שלב 4: טעינת שאילתת המוצרים

בשלב זה, עליך לטעון את השאילתה Products לתוך גליון עבודה של Excel.

  • בחר סגור את>הבית & טען. השאילתה מופיעה בגליון עבודה חדש של Excel.

ערסל: Power Query שלבים שנוצרו במשימה 1

בעת ביצוע פעילויות שאילתה ב- Power Query, שלבי שאילתה נוצרים ומפורטים בחלונית הגדרות שאילתה, ברשימה שלבים שהוחלו. לכל שלב בשאילתה יש נוסחת Power Query תואמת, הנקראת גם שפת "M". לקבלת מידע נוסף Power Query, ראה יצירת נוסחאות Power Query ב- Excel.

משימה שלב בשאילתה נוסחה
ייבוא חוברת עבודה של Excel מקור = Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
בחר את הטבלה Products לנווט = Source{[Item="Products",Kind="Table"]}[Data]
Power Query מזהה באופן אוטומטי סוגי נתונים של עמודות סוג שהשתנה = Table.TransformColumnTypes( Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})
הסרת עמודות אחרות כדי להציג רק עמודות רצויות הסרת עמודות אחרות = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

משימה 2: ייבוא נתוני הזמנות מהזנת OData

במשימה זו, עליך לייבא נתונים לחוברת העבודה של Excel מהזנת ה- OData לדוגמה Northwind ב- http://services.odata.org/Northwind/Northwind.svc, להרחיב את הטבלה Order_Details, להסיר עמודות, לחשב סכום שורה, להמיר OrderDate, לקבץ שורות לפי ProductID ושנה, לשנות את שם השאילתה ולבטל את הורדת השאילתה לחוברת העבודה של Excel.

שלב 1: התחברות להזנת OData

  1. בחר נתונים>קבל נתונים>ממקורות אחרים מהזנת>OData.
  2. בתיבת הדו-שיח הזנת OData, הזן את כתובת ה- URL של הזנת OData בשם Northwind.
  3. בחר אישור.
  4. בחלונית נווט , לחץ פעמיים על הטבלה Orders.

שלב 2: הרחבת Order_Details אישית

בשלב זה תרחיב את הטבלה Order_Details הקשורה לטבלה Orders, כדי לשלב את העמודות ProductID,‏ UnitPrice ו- Quantity מ- Order_Details בטבלה Orders. הפעולה הרחב משלבת עמודות מטבלה קשורה לטבלת נושא. בעת הפעלת השאילתה, שורות מהטבלה הקשורה (Order_Details) משולבות לשורות עם הטבלה הראשית (Orders).

ב Power Query, עמודה המכילה טבלה קשורה מכילה את הערך 'רשומה' או 'טבלה' בתא. עמודות אלה נקראות עמודות מובנות. רשומה מציינת רשומה קשורה יחידה ומייצגת קשר גומלין של יחיד ליחיד עם הנתונים הנוכחיים או הטבלה הראשית. טבלה מציינת טבלה קשורה ומייצגת קשר גומלין של אחד לרבים עם הטבלה הנוכחית או הראשית. עמודה מובנית מייצגת קשר גומלין במקור נתונים בעל מודל יחסי. לדוגמה, עמודה מובנית מציינת ישות עם שיוך מפתח זר בהזנת OData או בקשר גומלין של מפתח זר במסד SQL Server נתונים.

לאחר שתרחיב את Order_Details , שלוש עמודות חדשות ושורות נוספות יתווספו לטבלה Orders , אחת עבור כל שורה בטבלה המקוננת או הקשורה.

  1. בתצוגה מקדימה של נתונים, גלילה אופקית Order_Details העמודה.

  2. בעמודה Order_Details, בחר את סמל ההרחבה (הרחב ).

  3. בתפריט הנפתח הרחבה:

    1. בחר (בחר את כל העמודות) כדי לנקות את כל העמודות.

    2. בחר ProductID, UnitPriceו- Quantity.

    3. בחר אישור.
      הרחבת קישור הטבלה Order_Details

      הערה

      ב Power Query, באפשרותך להרחיב טבלאות המקושרות מעמודה לצבור את העמודות של הטבלה המקושרת לפני הרחבת הנתונים בטבלת הנושא. לקבלת מידע נוסף על אופן הביצוע של פעולות צבירה, ראה צבירת נתונים מעמודה.

שלב 3: הסרת עמודות אחרות כדי להציג רק עמודות מעניינות

בשלב זה תסיר את כל העמודות למעט OrderDate,‏ ProductID,‏ UnitPrice ו- Quantity. 

  1. בתצוגה מקדימה של נתונים, בחר את העמודות הבאות:

    1. בחר את העמודה הראשונה, OrderID.
    2. Shift+לחץ על העמודה האחרונה, מוביל.
    3. לחץ על Ctrl+העמודות OrderDate,‏ Order_Details.ProductID,‏ Order_Details.UnitPrice ו- Order_Details.Quantity.

  2. לחץ באמצעות לחצן העכבר הימני על כותרת עמודה שנבחרה ובחר הסר עמודות אחרות.

שלב 4: חישוב סכום השורה עבור כל Order_Details שורה

בשלב זה תיצור עמודה מותאמת אישית כדי לחשב את סכום השורה עבור כל שורה של Order_Details.

  1. בתצוגה מקדימה של נתונים, בחר את סמל הטבלה (סמל טבלה ) בפינה הימנית העליונה של התצוגה המקדימה.
  2. לחץ על הוסף עמודה מותאמת אישית.
  3. בתיבת הדו-שיח עמודה מותאמת אישית, בתיבה נוסחת עמודה מותאמת אישית, הזן [Order_Details.UnitPrice] * [Order_Details.Quantity].
  4. בתיבה שם עמודה חדשה, הזן סכום שורה.
  5. בחר אישור.

חישוב סכום השורה עבור כל שורה של Order_Details

שלב 5: המרת העמודה OrderDate year

בשלב זה תמיר את העמודה OrderDate כדי להציג את השנה של תאריך ההזמנה.

  1. בתצוגה מקדימה של נתונים, לחץ באמצעות לחצן העכבר הימני על העמודה OrderDate ובחר שנה המרה>.

  2. שנה את שם העמודה OrderDate ל- Year:

    1. לחץ פעמיים על העמודה OrderDate והזן Year או
    2. Right-Click בעמודה OrderDate , בחר שנה שם והזן שנה.

שלב 6: קיבוץ שורות לפי ProductID ושנה

  1. בתצוגה מקדימה של נתונים, בחרשנה Order_Details.ProductID.

  2. Right-Click אחת מהכותרות, ובחר קבץ לפי.

  3. בתיבת הדו-שיח קיבוץ לפי:

    1. בתיבת הטקסט שם עמודה חדשה, הזן Total Sales.
    2. בתפריט הנפתח פעולה, בחר באפשרות סכום.
    3. בתפריט הנפתח עמודה, בחר באפשרות Line Total.

  4. בחר אישור.
    תיבת הדו-שיח 'קיבוץ לפי' עבור פעולות צבירה

שלב 7: שינוי שם של שאילתה

לפני ייבוא נתוני המכירות ל- Excel, שנה את שם השאילתה:

  • בחלונית הגדרות שאילתה , בתיבה שם, הזן Total Sales.

תוצאות: שאילתה סופית עבור משימה 2

לאחר שתבצע את כל השלבים, תהיה לך שאילתת Total Sales על הזנת ה- OData בשם Northwind.

סך כל המכירות

ערסל: Power Query שלבים שנוצרו במשימה 2

בעת ביצוע פעילויות שאילתה ב- Power Query, שלבי שאילתה נוצרים ומפורטים בחלונית הגדרות שאילתה, ברשימה שלבים שהוחלו. לכל שלב בשאילתה יש נוסחת Power Query תואמת, הנקראת גם שפת "M". לקבלת מידע נוסף Power Query, ראה למד אודות Power Query אלה.

משימה שלב בשאילתה נוסחה
התחברות להזנת OData מקור = OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])
בחר טבלה ניווט = Source{[Name="Orders"]}[Data]
הרחבת הטבלה Order_Details הרחבת Order_Details = Table.ExpandTableColumn(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})
הסרת עמודות אחרות כדי להציג רק עמודות רצויות RemovedColumns = Table.RemoveColumns(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})
חישוב סכום השורה עבור כל שורה של Order_Details נוסף מותאם אישית = Table.AddColumn(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
= Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])
שינוי לשם משמעותי יותר, סה"כ לן עמודות ששמו השתנה = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})
המרת העמודה OrderDate להצגת השנה שנה מחולצת = Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})
שנה ל-
שמות בעלי משמעות רבה יותר, OrderDate ו- Year		 עמודות 1 ששמו השתנה Table.RenameColumns
(TransformedColumn,{{"OrderDate", "Year"}}‎)
קיבוץ שורות לפי ProductID ו- Year GroupedRows = Table.Group(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

משימה 3: שילוב השאילתות Products ו- Total Sales

Power Query מאפשר לך לשלב שאילתות מרובות על-ידי מיזוג או צירוף שלהן. פעולת המיזוג מבוצעת בכל שאילתת Power Query בעלת צורה טבלאית, ללא קשר למקור הנתונים שממנו מגיעים הנתונים. לקבלת מידע נוסף אודות שילוב מקורות נתונים, ראה שילוב שאילתות מרובות.

במשימה זו, עליך לשלב את השאילתות Products ו- Total Sales באמצעות שאילתת מיזוג ופעולה הרחב ולאחר מכן לטעון את השאילתה Total Sales per Product במודל הנתונים של Excel.

שלב 1: מיזוג ProductID לשאילתת Total Sales

  1. בחוברת העבודה של Excel, נווט אל השאילתה Products בכרטיסיה Products בגליון העבודה.

  2. בחר תא בשאילתה ולאחר מכן בחר מיזוג שאילתה>.

  3. בתיבת הדו-שיח מיזוג, בחר מוצרים כטבלה הראשית ובחר Total Sales כשאילתה משנית או קשורה למיזוג. Total Sales יהפוך לעמודה מובנית חדשה עם סמל הרחבה.

  4. כדי להתאים את Total Sales ל- Products לפי ProductID, בחר בעמודה ProductID מהטבלה Products, ובעמודה Order_Details.ProductID מהטבלה Total Sales.

  5. בתיבת הדו-שיח רמות פרטיות:

    1. בחר באפשרות ארגוני עבור רמת בידוד הפרטיות עבור שני מקורות הנתונים.
    2. בחר שמור.

  6. בחר אישור.

    הערה

    רמות פרטיות מונעות ממשתמש לשלב בשוגג נתונים ממקורות נתונים מרובים, שעשויים להיות פרטיים או ארגוניים. בהתאם לשאילתה, משתמש עלול לשלוח בשוגג נתונים ממקור הנתונים הפרטי למקור נתונים אחר העלול להיות זדוני. Power Query מנתח כל מקור נתונים ומסווג אותו לרמת הפרטיות המוגדרת: ציבורי, ארגוני ופרטי. לקבלת מידע נוסף אודות רמות פרטיות, ראה הגדרת רמות פרטיות.

    תיבת הדו-שיח 'מיזוג'

Result

הפעולה מיזוג יוצרת שאילתה. תוצאת השאילתה מכילה את כל העמודות מהטבלה הראשית (Products), ועמודה מובנית אחת של טבלה לטבלה הקשורה (Total Sales). בחר את הסמל הרחב כדי להוסיף עמודות חדשות לטבלה הראשית מהטבלה המשני או הקשורה.

מיזוג סופי

שלב 2: הרחבת עמודה ממוזגת

בשלב זה, תרחיב את העמודה הממוזגת עם השם NewColumn כדי ליצור שתי עמודות חדשות בשאילתה Products : Year ו - Total Sales.

  1. בתצוגה מקדימה של נתונים, בחר הרחב סמל (הרחב ) לצד NewColumn.

  2. ברשימה הנפתחת הרחב:

    1. בחר (בחר את כל העמודות) כדי לנקות את כל העמודות.
    2. בחר שנהוסך מכירות.
    3. בחר אישור.

  3. שנה את השם של שתי עמודות אלה ל- Year ול- Total Sales.

  4. כדי לגלות אילו מוצרים ובאותן שנים המוצרים צברו את הנפח הגבוה ביותר של מכירות, בחר מיין בסדר יורד לפי סך כל המכירות.

  5. שנה את השם של השאילתה ל- Total Sales per Product.

Result

הרחבת קישור טבלה

שלב 3: טעינת שאילתת Total Sales per Product למודל נתונים של Excel

בשלב זה, אתה טוען שאילתה במודל נתונים של Excel, כדי לבנות דוח המחובר לתצאת השאילתה. לאחר טעינת נתונים במודל הנתונים של Excel, באפשרותך להשתמש ב- Power Pivot כדי להמשיך בניתוח הנתונים.

  1. בחר סגור את>הבית & טען.
  2. בתיבת הדו-שיח ייבוא נתונים, הקפד לבחור הוסף נתונים אלה למודל הנתונים. לקבלת מידע נוסף אודות השימוש בתיבת דו-שיח זו, בחר את סימן השאלה (?).

Result

יש לך שאילתת Total Sales per Product המשלבת נתונים מקובץ ה- Products.xlsx ומזנת OData של Northwind. שאילתה זו מוחלת על מודל Power Pivot. בנוסף, שינויים בשאילתה משתנים מרועננים את הטבלה המתבצעת במודל הנתונים.

ערסל: Power Query שלבים שנוצרו במשימה 3

בעת ביצוע פעולות מיזוג שאילתה ב- Power Query, שלבי שאילתה נוצרים ומפורטים בחלונית הגדרות שאילתה, ברשימה שלבים שהוחלו. לכל שלב בשאילתה יש נוסחת Power Query תואמת, הנקראת גם שפת "M". לקבלת מידע נוסף Power Query, ראה למד אודות Power Query אלה.

משימה שלב בשאילתה נוסחה
מיזוג ProductID בשאילתת Total Sales מקור (מקור נתונים עבור פעולת המיזוג) = Table.NestedJoin(Products, {"ProductID"}, #"Total Sales", {"Order_Details.ProductID"}, "Total Sales", JoinKind.LeftOuter)
הרחבת עמודת מיזוג סכום מכירות מורחב = Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})
שינוי שם של שתי עמודות עמודות ששמו השתנה = Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})
מיון סכום מכירות בסדר עולה שורות ממוינות = Table.Sort(#"Renamed columns",{{"Total Sales", Order.Ascending}})

למידע נוסף

עזרה עבור Power Query for Excel