שמירת היסטוריית עדכוני שאילתת Power Query

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

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

שקול את הדוגמה הבאה.

נניח שיש לנו קובץ מהלקוח כנתוני קלט (בואו נקרא לזה, נניח, מָקוֹר) עם רשימת מוצרים שהוא רוצה לקנות בצורה של טבלה דינמית "חכמה" בשם בקשה:

בקובץ אחר (בוא נקרא לזה באנלוגיה מקלט) אנו יוצרים שאילתה פשוטה לייבא טבלה עם מוצרים מה-Source via נתונים - קבל נתונים - מקובץ - מחוברת עבודה של Excel (נתונים - קבל נתונים - מקובץ - מחוברת עבודה של Excel) והעלה את הטבלה שהתקבלה לגיליון:

אם בעתיד הלקוח יחליט לבצע שינויים בהזמנה בתיק שלו מָקוֹר, ולאחר מכן לאחר עדכון הבקשה שלנו (על ידי לחיצה ימנית או באמצעות נתונים - רענן הכל) נראה את הנתונים החדשים בקובץ מקלט - הכל סטנדרטי.

עכשיו בואו נוודא שבעת העדכון, הנתונים הישנים לא יוחלפו בחדשים, אלא יצורפו החדשים לישנים – ובתוספת תאריך-שעה, כך שניתן יהיה לראות מתי בוצעו השינויים הספציפיים הללו. עָשׂוּי.

שלב 1. הוספת תאריך-שעה לשאילתה המקורית

בואו נפתח בקשה בקשהמייבא את הנתונים שלנו מ מָקוֹר, והוסיפו לה עמודה עם התאריך-שעה של העדכון. כדי לעשות זאת, אתה יכול להשתמש בכפתור עמודה מותאמת אישית כרטיסייה הוספת עמודה (הוסף עמודה - עמודה מותאמת אישית), ולאחר מכן הזן את הפונקציה DateTime.LocalNow - אנלוגי של הפונקציה ה-TDATA (עַכשָׁיו) ב-Microsoft Excel:

לאחר לחיצה על OK אתה אמור לסיים עם עמודה יפה כמו זו (אל תשכח להגדיר את פורמט התאריך-שעה עבורה עם הסמל בכותרת העמודה):

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

שלב 2: שאילתה עבור נתונים ישנים

כעת בואו ניצור שאילתה נוספת שתשמש כמאגר ששומר את הנתונים הישנים לפני העדכון. בחירת תא כלשהו של הטבלה המתקבלת בקובץ מקלט, בחר בכרטיסייה נתונים פיקוד מתוך טבלה/טווח (נתונים - מטבלה/טווח) or עם עלים (מהגיליון):

אנחנו לא עושים כלום עם הטבלה שנטענת ב-Power Query, אנחנו קוראים לשאילתה, למשל, נתונים ישנים ולחץ בית — סגור וטען — סגור וטען ל... — צור חיבור בלבד (בית - סגור וטען - סגור וטען אל... - צור חיבור בלבד).

שלב 3. צירוף נתונים ישנים וחדשים

כעת נחזור לשאילתה המקורית שלנו בקשה ולהוסיף לו מלמטה את הנתונים הישנים מבקשת החוצץ הקודמת עם הפקודה דף הבית — הוסף בקשות (בית - הוסף שאילתות):

זה הכל!

נותר לחזור לאקסל דרך דף הבית — סגור והורד (בית - סגור וטען) ונסה כמה פעמים לעדכן את כל המבנה שלנו עם הכפתור עדכון כל כרטיסייה נתונים (נתונים - רענן הכל). עם כל עדכון, הנתונים החדשים לא יחליפו את הנתונים הישנים, אלא ידחפו אותם למטה, תוך שמירה על כל היסטוריית העדכונים:

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

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