יצירת נוסחאות של Power Query ב- Excel

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

1. רצועת עורך Power Query שבה אתה משתמש כדי לעצב את הנתונים שלך

2. החלונית 'שאילתות' שבה אתה משתמש כדי לאתר מקורות נתונים וטבלאות

3. תפריטים תלויי הקשר שהם קיצורי דרך נוחים לפקודות ברצועת הכלים

4. התצוגה המקדימה של הנתונים המציגה את תוצאות השלבים המוחלים על הנתונים

5. החלונית 'הגדרות שאילתה' המפרטת מאפיינים וכל שלב בשאילתה

מאחורי הקלעים, כל שלב בשאילתה מבוסס על נוסחה הגלויה בשורת הנוסחאות.

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

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

הליך

1. כדי לייבא את הנתונים, בחר נתונים > מהאינטרנט, הזן "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" בתיבה כתובת URL ולאחר מכן בחר אישור.

2. בתיבת הדו-שיח נווט, בחר את הטבלה תוצאות [עריכה] בצד ימין ולאחר מכן בחר המר נתונים בחלק התחתון. העורך Power Query מופיע.

3. כדי לשנות את שם השאילתה המוגדר כברירת מחדל, בחלונית הגדרות שאילתה, תחת מאפיינים, מחק את "תוצאות [עריכה]" ולאחר מכן הזן "UEFA champs".

4. כדי להסיר עמודות לא רצויות, בחר את העמודה הראשונה, הרביעית והעמודה החמישית ולאחר מכן בחר בית> הסר עמודה > הסר עמודות אחרות.

5. כדי להסיר ערכים לא רצויים, בחר עמודה1, בחר בית > החלף ערכים, הזן "פרטים" בתיבה ערכים לחיפוש ולאחר מכן בחר אישור.

6. כדי להסיר שורות המכילות את המילה "Year", בחר את חץ המסנן בעמודה1, נקה את תיבת הסימון לצד "Year" ולאחר מכן בחר אישור.

7. כדי לשנות את שמות כותרות העמודות, לחץ פעמיים על כל אחת מהן ולאחר מכן שנה את "Column1" ל- "Year", "Column4" ל- "Winner" ו- "Column5" ל- "Final Score".

8. כדי לשמור את השאילתה, בחר בית> סגור & טען.

Result

הטבלה הבאה היא סיכום של כל שלב שהוחל והנוסחה המתאימה.

שלב ומשימה של שאילתה	נוסחה
מקור התחברות למקור נתונים באינטרנט	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
ניווט בחר את הטבלה להתחברות	=Source{2}[Data]
סוג שהשתנה שינוי סוגי נתונים (פעולה Power Query אוטומטית)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
הסרת עמודות אחרות הסרת עמודות אחרות כדי להציג רק עמודות רצויות	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
ערך שהוחלף החלפת ערכים כדי לנקות ערכים בעמודה שנבחרה	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
שורות מסוננים סינון ערכים בעמודה	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
עמודות ששמו השתנה כותרות עמודות שהשתנו כדי להיות בעלות משמעות	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

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

הצגה או הסתרה של שורת הנוסחאות

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

• בחר הצג > פריסה > שורת הנוסחאות.

הוספתנוסחה בשורת הנוסחאות

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

2. בחלונית הגדרות שאילתה , תחת שלבים שהוחלו, בחר את השלב שברצונך לערוך.

3. בשורת הנוסחאות, אתר ושנה את ערכי הפרמטר ולאחר מכן בחר את סמל הזן או הקש Enter. לדוגמה, שנה נוסחה זו כך שתשתמש גם בעמודה2:
   
   לפני: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   אחרי:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. בחר את סמל או הקש Enter כדי לראות את התוצאות החדשות מוצגות בתצוגה המקדימה של הנתונים.

5. כדי לראות את התוצאה בגליון עבודה של Excel, בחר בית > סגור & טען.

יצירת נוסחה בשורת הנוסחאות

לדוגמה, עבור נוסחה פשוטה, נמיר ערך טקסט לסוג האות המתאים באמצעות הפונקציה Text.Proper.

1. כדי לפתוח שאילתה ריקה, ב- Excel, בחר נתונים> קבלת > ממקורות אחרים > ריקה. לקבלת מידע נוסף, ראה יצירה, טעינה או עריכה של שאילתה ב- Excel.

2. בשורת הנוסחאות, הזן=Text.Proper("text value")ולאחר מכן בחר את סמל הזן או הקש Enter.
   
   התוצאות מוצגות בתצוגה מקדימה של נתונים.

3. כדי לראות את התוצאה בגליון עבודה של Excel, בחר בית > סגור & טען.

תוצאה:

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

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

עריכת נוסחה באמצעות תיבת דו-שיח

שיטה זו משתמשת בתיבות דו-שיח שמשתנים בהתאם לשלב. אין צורך לדעת את תחביר הנוסחה.

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

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

3. בתיבת הדו-שיח, בצע את השינויים ולאחר מכן בחר אישור.

הוספת שלב

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

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

2. בחר את סמל הוספת שלב מימין ל שורת הנוסחאות. לחלופין, לחץ באמצעות לחצן העכבר הימני על שלב ולאחר מכן בחר הוסף שלב אחרי. נוסחה חדשה נוצרת בתבנית := <nameOfTheStepToReference>
   
   , כגון =Production.WorkOrder.

3. הקלד את הנוסחה החדשה באמצעות התבנית:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   לדוגמה, נניח שיש לך טבלה עם העמודה Gender וברצונך להוסיף עמודה עם הערך "Ms". או "מר", בהתאם למינו של האדם. הנוסחה תהיה:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

סידור מחדש של שלב

• בחלונית הגדרות שאילתות תחת שלבים שהוחלו, לחץ באמצעות לחצן העכבר הימני על השלב ולאחר מכן בחר הזז למעלה או הזז למטה.

מחיקת שלב

• בחר את הסמל מחק מימין לשלב, או לחץ באמצעות לחצן העכבר הימני על השלב ולאחר מכן בחר מחק או מחק עד סיום. סמל סמל 'מחק' זמין גם מימין ל שורת הנוסחאות.

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

לדוגמה, יש לך טבלת Excel, הנקראת Orders, עם עמודת ProductName שברצונך להמיר לסוג האות המתאים. 

לפני:

אחרי:

בעת יצירת שאילתה מתקדמת, אתה יוצר סידרה של שלבי נוסחת שאילתה בהתבסס על ביטוי let. השתמש בביטוי let כדי להקצות שמות ולחשב ערכים שהפסוקית in מפנה בהן, המגדירה את השלב. דוגמה זו מחזירה את אותה תוצאה כמו זו שבסעיף "יצירת נוסחה בשורת הנוסחאות".

let  
    Source = Text.Proper("hello world")
in  
    Source  

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

שלב 1: פתיחת העורך המתקדם

1. ב- Excel, בחר נתונים> לקבל נתונים > מקורות אחרים >ריקה. לקבלת מידע נוסף, ראה יצירה, טעינה או עריכה של שאילתה ב- Excel.

2. בתיבת עורך Power Query, בחר בית > עורך מתקדם, שנפתח עם תבנית של ביטוי let.

שלב 2: הגדרת מקור הנתונים

1. צור את ביטוי let באמצעות הפונקציה Excel.CurrentWorkbook באופן הבא:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. כדי לטעון את השאילתה לגליון עבודה, בחר סיום ולאחר מכן בחר בית> סגור & טען > סגור & טעינה.

תוצאה:

שלב 3: קידום השורה הראשונה לכותרות

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

2. בתיבת עורך Power Query, בחר בית > עורך מתקדם, שנפתח עם המשפט שיצרת בשלב 2: הגדרת מקור הנתונים.

3. בביטוי let, הוסף את הפונקציות #"First Row as Header" ו- Table.PromoteHeaders באופן הבא:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

4. כדי לטעון את השאילתה לגליון עבודה, בחר סיום ולאחר מכן בחר בית> סגור & טען > סגור & טעינה.

תוצאה:

שלב 4: שינוי כל ערך בעמודה לסוג האות המתאים

1. כדי לפתוח את השאילתה, מתוך גליון העבודה, בחר תא בנתונים ולאחר מכן בחר שאילתה> עריכה. לקבלת מידע נוסף, ראה יצירה, טעינה או עריכה של שאילתה ב- Excel.

2. בחלונית עורך Power Query, בחר בית> עורך מתקדם, שנפתח עם המשפט שיצרת בשלב 3: קידום השורה הראשונה לכותרות.

3. בביטוי let, המר כל ערך עמודה של ProductName לטקסט רגיל באמצעות הפונקציה Table.TransformColumns, תוך הפניה לשלב הקודם של נוסחת השאילתה "First Row as Header", הוספת #"Capitalized Each Word" למקור הנתונים ולאחר מכן הקצאת #"Capitalized Each Word" לתוצאה של in.
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. כדי לטעון את השאילתה לגליון עבודה, בחר סיום ולאחר מכן בחר בית> סגור & טען > סגור & טעינה.

תוצאה:

באפשרותך לשלוט באופן הפעולה של שורת הנוסחאות בשורת עורך Power Query עבור כל חוברות העבודה שלך.

הצגה או הסתרה של שורת הנוסחאות

1. בחר אפשרויות > קובץ והגדרות >שאילתה.

2. בחלונית הימנית, תחת כללי, בחר עורך Power Query.

3. בחלונית השמאלית, תחת פריסה, בחר או נקה את הצג את שורת הנוסחאות.

הפעלה או ביטול של M Intellisense

1. בחר אפשרויות > קובץ והגדרות >שאילתה .

2. בחלונית הימנית, תחת כללי, בחר עורך Power Query.

3. בחלונית השמאלית, תחת נוסחה, בחר או נקה את הפוך את M Intellisense לזמין בשורת הנוסחאות, בעורך מתקדם ובתיבת הדו-שיח של עמודה מותאמת אישית.

הערה    שינוי הגדרה זו יהפוך לתוקף בפעם הבאה שתפתח את עורך Power Query החלון.