Power Query – מלכודת 1000 השורות
תקציר המאמר:המאמר מסביר כיצד להימנע מ"מלכודת 1000 השורות" ב-Power Query,
הנובעת מכך שהכלי מציג את איכות הנתונים על סמך מדגם חלקי בלבד כברירת מחדל. הוא מפרט את הדרך לשינוי הגדרות הפרופיל כדי לקבל תמונה מלאה של כלל מסד הנתונים ולמנוע הופעת שגיאות בלתי צפויות לאחר סינון או מיון. בנוסף, מוצגת השוואה בין עבודה על מדגם חלקי לשיפור הביצועים לבין בדיקת ערכת הנתונים המלאה לצורך אבחון סופי. |
אם קרה לכם שאיכות העמודה הראתה שהנתונים שלכם תקינים ב-100% אבל אחרי סינון הופיעו שגיאות – המאמר הזה בשבילכם.
כשאנחנו עובדים עם Power Query, אחד הכלים היעילים ביותר שעומדים לרשותנו בשלב ניקוי הנתונים הוא תצוגת הנתונים בכרטיסיית תצוגה (View),
שם נוכל לבדוק את התפלגות הערכים בעמודה, את פרופיל העמודה ואת איכות העמודה (Column Quality),
שנותנת לנו מבט מהיר על אחוז השגיאות, הערכים הריקים והנתונים התקינים בכל עמודה:
אבל לעיתים, הכלי הזה עלול להטעות אותנו ולגרום לנו לחשוב שהנתונים שלנו במצב מצוין, עד לרגע שבו אנחנו מבצעים פעולה פשוטה כמו סינון,
ופתאום איכות העמודה מראה אחוזי שגיאות גבוהים, והטבלה מתמלאת בערכי Error.
למה זה קורה?
הסיבה לכך טמונה בהגדרת ברירת המחדל של Power Query.
כדי לשמור על ביצועים מהירים, Power Query טוען רק את 1000 השורות הראשונות ולא את כל מסד הנתונים.
הוא אפילו מתריע על כך בשורת המצב, אבל לטעמי האזהרה הזו לא מספיק בולטת.
הביטו בתחתית חלון העורך, שם תוכלו לראות את הכיתוב: "יצירת פרופילים של עמודות בהתבסס על 1000 השורות העליונות":
וזהו שורש הבעיה:
כשאתם מפעילים איכות עמודה, המדד שאתם רואים מתייחס רק לאלף השורות הראשונות.
אם השגיאות שלכם נמצאות בשורה 1,001 ומטה, המדד יראה 100% הצלחה (Valid).
ואז, אם ביצעתם פעולה כלשהו שמעלה את השורות השגויות למעלה, כמו סינון, מיון או מחיקת שורות,
השגיאות שהיו חבויות למטה נכנסות כעת לטווח של אלף השורות הראשונות ומדד איכות העמודה משתנה.
איך נמנעים מהפתעות?
כדי לקבל תמונה אמיתית ומלאה של איכות הנתונים בכל הקובץ (ולא רק בטעימה הראשונה שלו), עלינו לשנות את הגדרת הפרופיל של השאילתה.
לחצו על הכיתוב המציין שהפרופיל מבוסס על 1,000 השורות הראשונות.
בתפריט שנפתח, בחרו באפשרות: "יצירת פרופיל עמודות בהתבסס על ערכת הנתונים כולה"
מתי כדאי להשתמש בכל אחת מהאפשרויות?
חשוב לזכור שלכל בחירה יש מחיר:
מבוסס על 1,000 שורות: עדיף לעבודה שוטפת בקבצים גדולים מאוד, כדי שהעורך יגיב במהירות לכל שלב שאתם מוסיפים, אבל יכול לפספס שגיאות כי המדגם שלו מצומצם.
מבוסס על ערכת הנתונים כולה: מומלץ להפעיל בשלב האבחון הראשוני של הקובץ ובשלב הסופי לפני הטעינה לגיליון,
כדי לוודא שאין "הפתעות" שמחכות לכם בשורות התחתונות, אך משתמש במשאבי זכרון מוגברים.
במהלך העבודה, רצוי לשחק בין שתי האפשרויות, בהתאם לצורך.
לסיכום:
אל תסתמכו על איכות העמודה כעובדה מוגמרת מבלי לבדוק איזה טווח היא בודקת.
הבנה של מגבלות התצוגה המקדימה תחסוך לכם זמן יקר של חיפוש שגיאות בדיעבד.
שאלות ותשובות בנושא Power Query – מלכודת 1000 השורות
שאלה:
מדוע מדד איכות העמודה עלול להשתנות פתאום ולהציג שגיאות לאחר ביצוע פעולת סינון או מיון?
תשובה:
כברירת מחדל, המדד מתבסס רק על 1000 השורות הראשונות של הקובץ.
כאשר מבצעים פעולות כמו סינון, מיון או מחיקת שורות, שמעלות שורות "נמוכות" יותר (ממיקום 1001 ומטה) לתוך טווח אלף השורות הראשונות,
שגיאות שהיו חבויות מחוץ לטווח התצוגה נכנסות לחישוב ומופיעות במדד האיכות.
שאלה:
כיצד ניתן לוודא שפרופיל העמודה משקף את כל הנתונים בקובץ ולא רק מדגם חלקי?
תשובה:
יש ללחוץ על הכיתוב בשורת המצב בתחתית חלון העורך, המציין כי הפרופיל מבוסס על 1000 השורות הראשונות.
בתפריט שייפתח, יש לבחור באפשרות "יצירת פרופיל עמודות בהתבסס על ערכת הנתונים כולה".
שאלה:
מתי מומלץ להשתמש בבדיקת ערכת הנתונים המלאה ומתי עדיף להסתפק ב-1000 השורות הראשונות?
תשובה:
השימוש ב-1000 שורות מומלץ לעבודה שוטפת בקבצים גדולים מאוד כדי לשמור על מהירות תגובה של העורך.
לעומת זאת, בדיקה של ערכת הנתונים המלאה מומלצת בשלב האבחון הראשוני של הקובץ ובשלב הסופי לפני הטעינה לגיליון,
כדי לוודא שאין שגיאות נסתרות בשורות התחתונות.