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

במשימה זו, תייבא מוצרים מהקובץ 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"})

במשימה זו, עליך לייבא נתונים לחוברת העבודה של 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. בחר אישור.

      

      הערה: ב 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. בחר אישור.

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

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

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

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

   1. לחץ פעמיים על העמודה OrderDate והזן Year או

   2. Right-Click בעמודה OrderDate , בחר שנה שם והזן שנה.

שלב 6: קיבוץ שורות לפי ProductID ו- Year

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}})

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}})