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

יצירת נוסחה פשוטה

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

1. בכרטיסיית רצועת הכלים POWER QUERY, בחר ממקורות אחרים > שאילתה ריקה.

   

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

3. Power Query מציג את התוצאות בחלונית תוצאות הנוסחה.

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

התוצאה תיראה כך בגליון עבודה:

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

יצירת נוסחה מתקדמת

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

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

הטבלה המקורית נראית כך:

אתה מעוניין שהטבלה המתקבלת תיראה כך:

אנו נבנה את שלבי נוסחת השאילתה כדי לשנות את הטבלה המקורית כך שהערכים העמודה ProductName יהיו ברישיות המתאימה.

דוגמה לשאילתה מתקדמת באמצעות העורך המתקדם

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

• צור סידרה של שלבי נוסחת שאילתה שמתחילים במשפט let. שים לב ששפת הנוסחאות של Power Query תלויית רישיות.

• כל שלב נוסחת שאילתה מתבסס על השלב הקודם על-ידי הפניה לשלב לפי שמו.

• צור פלט של שלב נוסחת שאילתה באמצעות המשפט in. בדרך כלל, שלב השאילתה האחרון משמש כתוצאת ערכת הנתונים הסופית של in.

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

1. בכרטיסיית רצועת הכלים POWER QUERY, בחר ממקורות אחרים > שאילתה ריקה.

2. בעורך השאילתות, בחר עורך מתקדם.

   

3. יוצג עורך מתקדם.

   

שלב 2 – הגדרת המקור המקורי

בעורך מתקדם:

1. השתמש במשפט let שמקצה את המקור באמצעות הנוסחה Source = Excel.CurrentWorkbook()‎. נוסחה זו תשתמש בטבלת Excel כמקור הנתונים. לקבלת מידע נוסף על הנוסחה Excel.CurrentWorkbook()‎, ראה Excel.CurrentWorkbook.

2. הקצה את Source לתוצאה של in.

   let Source =
   Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in Source

3. השאילתה המתקדמת שלך תיראה כך בעורך מתקדם.

   

4. כדי להציג את התוצאות בגליון עבודה:

   1. לחץ על בוצע.

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

התוצאה נראית כך בגליון עבודה:

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

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

1. הוסף את הנוסחה ‎#"First Row as Header" = Table.PromoteHeaders()‎ לשלבי נוסחת השאילתה וצור הפניה ל- Source כמקור הנתונים. לקבלת מידע נוסף על הנוסחה Table.PromoteHeaders()‎, ראה Table.PromoteHeaders.

2. הקצה את ‎#"First Row as Header"‎ לתוצאה של in.

   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source)
   in
       #"First Row as Header"

התוצאה נראית כך בגליון עבודה:

שלב 4 – שינוי כל אחד מהערכים בעמודה לרישיות המתאימה

כדי להמיר כל אחד מהערכים בעמודה ProductName לטקסט רגיל, עליך להשתמש בנוסחה Table.TransformColumns()‎ וליצור הפניה לשלב נוסחת השאילתה ‎"First Row as Header"‎. עליך לעשות זאת בעורך מתקדם:

1. הוסף את הנוסחה ‎#"Capitalized Each Word" = Table.TransformColumns()‎ לשלבי נוסחת השאילתה וצור הפניה ל- ‎#"First Row as Header"‎ כמקור הנתונים. לקבלת מידע נוסף על הנוסחה Table.TransformColumns()‎, ראה Table.TransformColumns.

2. הקצה את ‎#"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"

התוצאה הסופית תשנה כל אחד מהערכים בעמודה ProductName לרישיות המתאימה ותיראה כך בגליון עבודה:

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