Power Query באקסל: מדריך לניקוי ואיחוד נתונים

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

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

במדריך הזה נסביר:

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

מה זה Power Query ולמה הוא חוסך שעות

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

בעולם הנתונים לתהליך כזה קוראים ETL, ראשי תיבות של Extract, Transform, Load: שליפה, המרה וטעינה. Power Query הוא כלי ETL מלא, רק שהוא בנוי למשתמשים עסקיים ולא למתכנתים. במקום לכתוב קוד לוחצים על כפתורים בתפריט, והכלי רושם בעצמו את השלבים בשפה פנימית שנקראת M.

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

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

מה Power Query יודע לעשות

את העבודה ב-Power Query נוח לחלק לשלושה שלבים: התחברות למקור, המרה וטעינה.

התחברות למקורות נתונים

Power Query מתחבר כמעט לכל פורמט שעסק פוגש ביום-יום:

• קובצי Excel, CSV, TXT, JSON, XML ו-PDF (טבלאות מתוך PDF);
• דפי אינטרנט עם טבלאות וכתובות API שמחזירות JSON;
• מסדי נתונים כמו SQL Server, Access, MySQL ו-PostgreSQL (חלק מהחיבורים דורשים התקנת דרייבר);
• קבצים ב-SharePoint וב-OneDrive;
• תיקייה שלמה של קבצים, למשל כל דוחות החודש שנשמרו באותה תיקייה.

אפשר להתחבר לכמה מקורות במקביל ולשלב ביניהם. למשל, לקחת את רשימת ההזמנות מהחנות ולהוסיף לה את מחירי העלות מקובץ הספק.

המרה וניקוי

אחרי שהנתונים נטענו לעורך, אפשר:

• לשנות סוג נתונים, למשל מספר לטקסט או טקסט לתאריך;
• להסיר רווחים מיותרים, שורות ריקות, עמודות מיותרות וכפילויות;
• לשנות אותיות גדולות וקטנות בטקסט באנגלית;
• למיין ולסנן, לשנות את סדר העמודות ולשנות להן שמות;
• לפצל עמודה לפי תו מפריד, או לחבר כמה עמודות לטקסט אחד;
• להוסיף עמודות מחושבות, למשל מחיר כולל מע"מ או מספר ימים מאז ההזמנה;
• לקבץ ולסכם, למשל סך המכירות לפי חודש וערוץ;
• להשלים ערכים מטבלה אחרת, בדומה לפונקציות VLOOKUP ו-XLOOKUP.

כל השינויים קורים בתוך Power Query. קובצי המקור נשארים כמו שהם, ולכן אין סכנה לקלקל את הנתונים המקוריים.

טעינה

את התוצאה אפשר לטעון כטבלה לגיליון, ליצור ממנה טבלת ציר (PivotTable) או תרשים ציר, או להוסיף אותה למודל הנתונים ולבנות עליו דוחות שמשלבים כמה טבלאות.

איפה מוצאים את Power Query באקסל

Power Query לא דורש התקנה נפרדת בגרסאות העדכניות. בגרסאות הישנות של 2010 ו-2013 הוא הותקן כתוסף, אבל הגרסאות האלה, וגם Office 2016 ו-2019, כבר לא מקבלות עדכוני אבטחה ממיקרוסופט. אם בעסק עדיין עובדים עליהן, זו סיבה טובה לעבור ל-Microsoft 365 או ל-Office 2024.

Excel ל-Windows

זו הגרסה עם האפשרויות המלאות. בכרטיסייה Data (נתונים) יש קבוצה בשם Get & Transform Data, ובה הכפתור Get Data. דרכו בוחרים את סוג המקור, וממנו נפתח עורך Power Query. בממשק בעברית השמות מתורגמים, אבל המיקום זהה.

Excel ל-Mac

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

Excel באינטרנט ו-Power BI

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

דוגמה מעשית: ניקוי קטלוג מוצרים

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

שלב 1. טוענים את הקובץ

בכרטיסייה Data לוחצים Get Data, בוחרים From File ואז From Excel Workbook, ובוחרים את הקובץ. נפתח חלון Navigator, ובו מסמנים את הגיליון או הטבלה שרוצים לטעון. במקום Load לוחצים Transform Data, וכך נפתח עורך Power Query.

שלב 2. מתקנים את סוג הנתונים

Power Query מזהה סוגי נתונים אוטומטית, ולפעמים טועה. מק"ט כמו 004512 מזוהה כמספר, והאפסים בהתחלה נעלמים. אותה בעיה בדיוק פוגעת במספרי טלפון ישראליים: 0501234567 הופך ל-501234567.

כדי לתקן, לוחצים על הסמל שמשמאל לשם העמודה (בעמודה מספרית מופיע שם "123") ובוחרים Text. עדיף לעשות את זה לפני כל שלב אחר, כי אחרי שהאפסים נמחקו קשה לשחזר אותם. אם צריך, מוחקים את שלב ה-Changed Type האוטומטי שהכלי הוסיף ומגדירים את הסוג בעצמכם.

שלב 3. משנים אותיות גדולות וקטנות

עמודת הקטגוריה כתובה כולה באותיות קטנות, למשל accessories. לוחצים לחיצה ימנית על כותרת העמודה, בוחרים Transform ואז Capitalize Each Word, וכל מילה מתחילה באות גדולה. האפשרות רלוונטית לטקסט באנגלית. בעברית אין אותיות גדולות, אבל אותו תפריט שימושי לפעולות אחרות.

שלב 4. מסירים רווחים ותווים מיותרים

לחיצה ימנית על עמודת שם המוצר, Transform ואז Trim. הפעולה מוחקת רווחים בתחילת הטקסט ובסופו. הפעולה Clean באותו תפריט מסירה תווים בלתי נראים, שמגיעים לעיתים קרובות מהעתקה מאתרים וממערכות ישנות.

שלב 5. מסירים כפילויות ומסננים

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

שלב 6. בודקים את השלבים שנשמרו

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

איחוד קבצים ומיזוג טבלאות

הכוח האמיתי של Power Query מתגלה כשיש יותר מקובץ אחד. שתי הפעולות החשובות כאן הן Append ו-Merge.

איחוד קבצים מתיקייה (Append)

נניח שבכל חודש אתם מורידים ייצוא של הזמנות מהחנות. במקום להעתיק ולהדביק את כולם לגיליון אחד, שומרים את כל הקבצים באותה תיקייה ומתחברים אליה דרך Get Data, From File ואז From Folder. Power Query מציע לשלב את הקבצים, ומחבר את כולם לטבלה אחת. בחודש הבא מוסיפים קובץ חדש לתיקייה, לוחצים רענון, והוא נכנס לדוח.

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

מיזוג טבלאות (Merge)

Merge Queries מחברת שתי טבלאות לפי עמודה משותפת, כמו VLOOKUP, רק בלי נוסחאות שנשברות. למשל:

• טבלת לידים מטופס האתר וטבלת עסקאות מה-CRM, שמחוברות לפי כתובת המייל;
• טבלת הזמנות וטבלת עלויות מהספק, שמחוברות לפי מק"ט, כדי לחשב רווח גולמי;
• דוח הוצאות פרסום לפי קמפיין ודוח לידים לפי קמפיין, כדי לחשב עלות לליד.

בחלון המיזוג בוחרים את שתי הטבלאות, מסמנים בכל אחת את עמודת המפתח ובוחרים סוג חיבור. הנפוץ ביותר הוא Left Outer: כל השורות מהטבלה הראשונה, ולידן הנתונים התואמים מהשנייה. אחר כך פורשים את העמודה החדשה ובוחרים אילו שדות להוסיף.

עוד פעולות שימושיות

• Unpivot Columns הופכת טבלה "רחבה", עם עמודה לכל חודש, לטבלה "ארוכה" שקל לסכם ולבנות ממנה תרשים;
• Group By מסכמת נתונים לפי קטגוריה, למשל מכירות לפי ערוץ;
• Fill Down משלימה ערכים בתאים ריקים מהשורה שמעליהם, בעיה נפוצה בדוחות שיוצאו ממערכות הנהלת חשבונות;
• Column From Examples מאפשרת לכתוב כמה דוגמאות לתוצאה הרצויה, ו-Power Query מנחש את הנוסחה בעצמו.

טעינת התוצאה ורענון הדוח

כשהטבלה מוכנה, לוחצים Close & Load כדי לטעון אותה לגיליון חדש, או Close & Load To כדי לבחור יעד אחר:

• Table – טבלה רגילה בגיליון, שמתעדכנת ברענון;
• PivotTable Report – טבלת ציר שמבוססת ישירות על השאילתה;
• Only Create Connection – השאילתה נשמרת בלי לטעון נתונים לגיליון. מתאים לטבלאות עזר שמשמשות רק למיזוג;
• Add this data to the Data Model – טעינה למודל הנתונים, שם אפשר לקשר בין טבלאות ולעבוד עם יותר שורות ממה שגיליון מאפשר.

איך מרעננים

בכרטיסייה Data לוחצים Refresh All, וכל השאילתות בקובץ רצות מחדש. במאפייני השאילתה (Query Properties) אפשר להגדיר רענון אוטומטי בפתיחת הקובץ, או רענון כל כמה דקות כשהקובץ פתוח.

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

Power Query בעסק ישראלי: תרחישים ודגשים

תרחישים נפוצים

• דוח לידים שבועי. ייצוא מטופס האתר, מ-Meta Lead Ads ומ-Google Ads מתאחד לטבלה אחת עם עמודת מקור, טלפונים מנורמלים וללא כפילויות.
• התאמת הזמנות ותשלומים. ייצוא הזמנות מהחנות (WooCommerce, Shopify או Webflow Ecommerce) מתמזג עם דוח מחברת הסליקה לפי מספר הזמנה, וכך רואים מה לא שולם או לא סופק.
• דוחות הכנסות. תוכנות הנהלת חשבונות והפקת חשבוניות מאפשרות בדרך כלל ייצוא ל-Excel או ל-CSV. Power Query מסכם את הנתונים לפי חודש, לקוח או סוג שירות, ומוסיף עמודות של סכום לפני מע"מ ואחריו.
• מלאי ומחירים מספקים. קובצי ספקים במבנים שונים מקבלים מבנה אחיד לפני עדכון הקטלוג באתר.

עברית, קידוד ותאריכים

• קידוד CSV. קובץ CSV בעברית שמוצג כג'יבריש נשמר לרוב בקידוד שונה ממה שהכלי ניחש. בחלון הטעינה של הקובץ יש שדה File Origin: נסו 65001: Unicode (UTF-8), ואם הקובץ ישן, 1255: Hebrew (Windows).
• תאריכים. מערכות אמריקאיות מייצאות תאריכים בפורמט חודש/יום/שנה, ובישראל עובדים עם יום/חודש/שנה. כדי שה-3 בפברואר לא יהפוך ל-2 במרץ, בוחרים בעמודה Change Type ואז Using Locale, ומגדירים את האזור של המקור.
• מספרי טלפון. הגדירו את העמודה כטקסט, הסירו מקפים ורווחים בעזרת Replace Values, ואם צריך פורמט בינלאומי, הוסיפו עמודה שמחליפה את האפס הראשון ב-972.
• שמות עמודות בעברית. הם עובדים בלי בעיה, אבל אם השאילתה תשמש גם כלים אחרים, כמו n8n או מסד נתונים, נוח יותר לתת לעמודות שמות קצרים באנגלית.

פרטיות ואבטחת מידע

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

טעויות נפוצות ומתי לעבור לכלי אחר

משנים את קובץ המקור במקום את השאילתה

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

סומכים על זיהוי הסוג האוטומטי

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

משנים את מבנה הייצוא בלי לעדכן את השאילתה

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

בונים הכול בשאילתה אחת ארוכה

עשרות שלבים בשאילתה אחת קשה לבדוק. עדיף לפצל: שאילתה לכל מקור, שאילתה שמאחדת, ושאילתה שמסכמת לדוח.

מתי Power Query כבר לא מספיק

Power Query מצוין כשאדם אחד מכין דוח בקצב קבוע. אבל יש סימנים שהגיע הזמן לשלב הבא:

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

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

איך RJL Studio עוזרת לסדר את הנתונים של העסק

ב-RJL Studio אנחנו מחברים בין האתר, הטפסים, ה-CRM, מערכות הסליקה ופלטפורמות הפרסום, כך שהנתונים מגיעים למקום אחד בלי העתק-הדבק. כשמספיק דוח אקסל טוב, אנחנו עוזרים לבנות אותו נכון. כשצריך יותר, אנחנו בונים תהליכים אוטומטיים ב-n8n, Make ו-Zapier ואינטגרציות API, שמעבירים לידים, הזמנות ונתוני קמפיינים ישירות למערכות שלכם.

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

שאלות נפוצות על Power Query

האם Power Query עולה כסף?

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

מה ההבדל בין Power Query ל-Power Pivot?

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

צריך לדעת לתכנת כדי לעבוד עם Power Query?

לא. רוב העבודה נעשית בלחיצות בתפריט, והכלי כותב את הקוד בשפת M בעצמו. מי שרוצה יכול לפתוח את Advanced Editor ולערוך את הקוד ישירות, אבל זה לא הכרחי.

האם Power Query עובד עם Google Sheets?

אין באקסל מחבר ישיר ל-Google Sheets. אפשר להוריד את הגיליון כקובץ Excel או CSV ולטעון אותו, ובעבודה שוטפת בין Google Sheets לאקסל או ל-CRM נוח יותר להשתמש בכלי אוטומציה כמו n8n, Make או Zapier.

מה עדיף: Power Query או אוטומציה?

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