בערכת לימוד זו, תוכל להשתמש עורך Power Query של Power Query כדי לייבא נתונים מקובץ Excel מקומי המכיל פרטי מוצר ומהזנת OData המכילה מידע על הזמנות מוצרים. תבצע שלבי שינוי וצבירה, ותשלב נתונים משני המקורות כדי להפיק דוח "סה"כ מכירות למוצר ושנה".
כדי לבצע ערכת לימוד זו, דרושה לך חוברת העבודה Products . בתיבת הדו-שיח שמירה בשם, תן לקובץ את השם Products and Orders.xlsx.
משימה 1: ייבוא מוצרים לחוברת עבודה של Excel
במשימה זו תייבא מוצרים מהקובץ Products and Orders.xlsx (ששמו הורד ושמו השתנה לעיל) לתוך חוברת עבודה של Excel, תקדם שורות לכותרות העמודות, תסיר עמודות ותטען את השאילתה בגליון עבודה.
שלב 1: חיבור לחוברת עבודה של Excel
- צור חוברת עבודה של Excel.
- בחר נתונים> מקבליםנתונים>מקובץ>מחוברת עבודה.
- בתיבת הדו-שיח ייבוא נתונים , אתר ואתר את הקובץ Products.xlsx שהורדת ולאחר מכן בחר פתח.
- בחלונית 'נווט' , לחץ פעמיים על הטבלה Products . עורך Power Query מופיע.
כברירת מחדל, Power Query מוסיף באופן אוטומטי כמה שלבים לנוחותך. בדוק כל שלב תחת 'שלבים שהוחלו' בחלונית 'הגדרות שאילתה' לקבלת מידע נוסף.
- לחץ באמצעות לחצן העכבר הימני על המקור שלב ובחר ערוך הגדרות. שלב זה נוצר בעת ייבוא חוברת העבודה.
- לחץ באמצעות לחצן העכבר הימני על שלב הניווט ובחר ערוך הגדרות. שלב זה נוצר בעת בחירת הטבלה מתיבת הדו-שיח 'ניווט '.
- לחץ באמצעות לחצן העכבר הימני על סוג שהשתנה שלב ובחר ערוך הגדרות. שלב זה נוצר על-ידי Power Query, אשר הסיק מהם סוגי הנתונים של כל עמודה. בחר את החץ למטה משמאל לשורת הנוסחאות כדי לראות את הנוסחה המלאה.
שלב 3: הסרת עמודות אחרות כדי להציג רק עמודות רצויות
בשלב זה תסיר את כל העמודות למעט ProductID, ProductName, CategoryID ו- QuantityPerUnit.
- ב'תצוגה מקדימה של נתונים', בחר את העמודות ProductID,ProductName, CategoryID ו- QuantityPerUnit (השתמש ב- Ctrl+לחיצה או ב- Shift+לחיצה).
- בחר 'הסר עמודות',>'הסר עמודות אחרות'.
שלב 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 ו- Year, תשנה את שם השאילתה ותהפוך את הורדת השאילתה לחוברת העבודה של Excel ללא זמינה.
- בחר נתונים> מקבליםנתונים>ממקורות> אחריםמהזנת OData.
- בתיבת הדו-שיח הזנת OData, הזן את כתובת ה- URL של הזנת OData בשם Northwind.
- בחר אישור.
- בחלונית 'נווט' , לחץ פעמיים על הטבלה 'הזמנות '.
שלב 2: הרחבת טבלת Order_Details
בשלב זה תרחיב את הטבלה Order_Details הקשורה לטבלה Orders, כדי לשלב את העמודות ProductID, UnitPrice ו- Quantity מ- Order_Details בטבלה Orders. הפעולה הרחב משלבת עמודות מטבלה קשורה לטבלת נושא. כאשר השאילתה פועלת, שורות מהטבלה הקשורה (Order_Details) משולבות בשורות עם הטבלה הראשית (Orders).
ב- Power Query, עמודה המכילה טבלה קשורה מכילה את הערך רשומה או טבלה בתא. עמודות אלה נקראות 'עמודות מובנות'. רשומה מציינת רשומה קשורה יחידה ומייצגת קשר גומלין של יחיד ליחיד עם הנתונים הנוכחיים או הטבלה הראשית. טבלה מציינת טבלה קשורה ומייצגת קשר גומלין של יחיד לרבים עם הטבלה הנוכחית או הטבלה הראשית. עמודה מובנית מייצגת קשר גומלין במקור נתונים בעל מודל יחסי. לדוגמה, עמודה מובנית מציינת ישות בעלת שיוך מפתח זר בהזנת OData או קשר גומלין של מפתח זר במסד נתונים של SQL Server.
לאחר הרחבת הטבלה Order_Details , שלוש עמודות חדשות ושורות נוספות מתווספות לטבלה Orders , אחת לכל שורה בטבלה המקוננת או הקשורה.
ב'הצגה לפני נתונים', גלול אופקית לעמודה Order_Details.
בעמודה Order_Details , בחר את סמל ההרחבה (
).בתפריט הנפתח הרחבה:
בחר (בחר את כל העמודות) כדי לנקות את כל העמודות.
בחר ProductID,UnitPrice ו - Quantity.
בחר אישור.
הערה
ב- Power Query, באפשרותך להרחיב טבלאות המקושרות מעמודה ולצבור את העמודות של הטבלה המקושרת לפני הרחבת הנתונים בטבלת הנושא. לקבלת מידע נוסף על אופן הביצוע של פעולות צבירה, ראה צבירת נתונים מעמודה.
שלב 3: הסרת עמודות אחרות כדי להציג רק עמודות רצויות
בשלב זה תסיר את כל העמודות למעט OrderDate, ProductID, UnitPrice ו- Quantity.
ב'תצוגה מקדימה של נתונים', בחר את העמודות הבאות:
- בחר את העמודה הראשונה, OrderID.
- Shift+לחץ על העמודה האחרונה, מוביל.
- לחץ על Ctrl+העמודות OrderDate, Order_Details.ProductID, Order_Details.UnitPrice ו- Order_Details.Quantity.
לחץ באמצעות לחצן העכבר הימני על כותרת עמודה ובחר 'הסר עמודות אחרות'.
שלב 4: חישוב סכום השורה עבור כל שורה Order_Details
בשלב זה תיצור עמודה מותאמת אישית כדי לחשב את סכום השורה עבור כל שורה של Order_Details.
-
ב'תצוגה מקדימה של נתונים', בחר את סמל הטבלה (
') בפינה הימנית העליונה של התצוגה המקדימה. - לחץ על הוסף עמודה מותאמת אישית.
- בתיבת הדו-שיח עמודה מותאמת אישית , בתיבת הנוסחה עמודה מותאמת אישית , הזן [Order_Details.UnitPrice] * [Order_Details.Quantity].
- בתיבה שם עמודה חדשה , הזן Line Total.
- בחר אישור.
שלב 5: המרת עמודת השנה OrderDate
בשלב זה תמיר את העמודה OrderDate כדי להציג את השנה של תאריך ההזמנה.
ב'תצוגה מקדימה של נתונים', לחץ באמצעות לחצן העכבר הימני על העמודה OrderDate ובחר 'המר>שנה'.
שנה את שם העמודה OrderDate ל- Year:
- לחץ פעמיים על העמודה OrderDate והזן Year או
- Right-Click בעמודה OrderDate , בחר שנה שם והזן שנה.
שלב 6: קיבוץ שורות לפי ProductID ו- Year
ב'תצוגה מקדימה של נתונים', בחר 'שנה' ו- Order_Details.ProductID.
Right-Click אחת מהכותרות ובחר קיבוץ לפי.
בתיבת הדו-שיח קיבוץ לפי:
- בתיבת הטקסט שם עמודה חדשה, הזן Total Sales.
- בתפריט הנפתח פעולה, בחר באפשרות סכום.
- בתפריט הנפתח עמודה, בחר באפשרות Line Total.
בחר אישור.
לפני שתייבא את נתוני המכירות ל- Excel, שנה את שם השאילתה:
- בחלונית 'הגדרות שאילתה ', בתיבה 'שם ', הזן 'סך כל המכירות'.
תוצאות: שאילתה סופית עבור פעילות 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]) |
| שנה לשם בעל משמעות רבה יותר, lne סך הכל | עמודות ששמן השתנה | = Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}}) |
| המרת העמודה OrderDate להצגת השנה | שנה מחולצת | = Table.TransformColumns(#"שורות מקובצות",{{"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
בחוברת העבודה של Excel, נווט אל השאילתה Products בכרטיסיית גליון העבודה Products .
בחר תא בשאילתה ולאחר מכן בחר 'מיזוג שאילתה>'.
בתיבת הדו-שיח מיזוג , בחר Products כטבלה הראשית, ובחר Total Sales כשאילתה המשנית או הקשורה למיזוג. Total Sales תהפוך לעמודה מובנית חדשה עם סמל הרחבה.
כדי להתאים את Total Sales ל- Products לפי ProductID, בחר בעמודה ProductID מהטבלה Products, ובעמודה Order_Details.ProductID מהטבלה Total Sales.
בתיבת הדו-שיח רמות פרטיות:
- בחר באפשרות ארגוני עבור רמת בידוד הפרטיות עבור שני מקורות הנתונים.
- בחר שמור.
בחר אישור.
הערה
רמות פרטיות מונעות ממשתמש לשלב בשוגג נתונים ממקורות נתונים מרובים, שעשויים להיות פרטיים או ארגוניים. בהתאם לשאילתה, משתמש עלול לשלוח בשוגג נתונים ממקור הנתונים הפרטי למקור נתונים אחר העלול להיות זדוני. Power Query מנתח כל מקור נתונים ומסווג אותו לרמת הפרטיות המוגדרת: ציבורי, ארגוני ופרטי. לקבלת מידע נוסף אודות רמות פרטיות, ראה הגדרת רמות פרטיות.
Result
הפעולה 'מזג ' יוצרת שאילתה. תוצאת השאילתה מכילה את כל העמודות מהטבלה הראשית (Products), ועמודה מובנית בודדת של טבלה לטבלה הקשורה (Total Sales). בחר בסמל ' הרחב ' כדי להוסיף עמודות חדשות לטבלה הראשית מהטבלה המשנית או הקשורה.
בשלב זה תרחיב את העמודה הממוזגת בשם NewColumn כדי ליצור שתי עמודות חדשות בשאילתת Products : Year ו - Total Sales.
ב'תצוגה מקדימה של נתונים', בחר סמל 'הרחב' (
) לצד NewColumn.ברשימה הנפתחת 'הרחב ':
- בחר (בחר את כל העמודות) כדי לנקות את כל העמודות.
- בחר שנהוסך מכירות.
- בחר אישור.
שנה את השם של שתי עמודות אלה ל- Year ול- Total Sales.
כדי לגלות אילו מוצרים קיבלו את נפח המכירות הגבוה ביותר ובאילו שנים, בחר 'מיין בסדר יורד לפי סך כל המכירות'.
שנה את השם של השאילתה ל- Total Sales per Product.
Result
שלב 3: טעינת שאילתת Total Sales per Product במודל נתונים של Excel
בשלב זה, תטען שאילתה למודל נתונים של Excel כדי לבנות דוח המחובר לתוצאת השאילתה. לאחר טעינת הנתונים למודל הנתונים של Excel, ניתן להשתמש ב- Power Pivot כדי להמשיך בניתוח הנתונים.
- בחר בית,>סגור & טען.
- בתיבת הדו-שיח ייבוא נתונים , הקפד לבחור באפשרות הוסף נתונים אלה למודל הנתונים. לקבלת מידע נוסף על השימוש בתיבת דו-שיח זו, בחר בסימן השאלה (?).
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}}) |