פרמטרים של נתיבים ב- Power Query

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

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

ניסוח הבעיה

נניח שיש לנו בתיקייה E:דוחות מכירות משקר הקובץ 100 המוצרים המובילים.xls, שהוא העלאה ממסד הנתונים הארגוני או מערכת ה-ERP שלנו (1C, SAP וכו') קובץ זה מכיל מידע על פריטי הסחורה הפופולריים ביותר ונראה כך בפנים:

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

לכן, ליד קובץ זה באותה תיקיה, אנו יוצרים קובץ חדש נוסף Handler.xlsx, שבה ניצור שאילתת Power Query שתטען נתונים מכוערים מקובץ העלאת המקור 100 המוצרים המובילים.xls, וסדר אותם:

הגשת בקשה לקובץ חיצוני

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

בואו נחזיר אותם לשגרה:

1. מחק שורות ריקות עם דף הבית — מחק שורות — מחק שורות ריקות (בית - הסר שורות - הסר שורות ריקות).
2. מחק את 4 השורות העליונות המיותר דרך דף הבית — מחק שורות — מחק שורות עליונות (בית - הסר שורות - הסר שורות עליונות).
3. הרם את השורה הראשונה לכותרת הטבלה בעזרת הכפתור השתמש בשורה הראשונה ככותרות כרטיסייה עמוד הבית (בית - השתמש בשורה הראשונה ככותרת).
4. הפרד את המאמר בן חמש הספרות משם המוצר בעמודה השנייה באמצעות הפקודה עמודה מפוצלת כרטיסייה טרנספורמציה (טרנספורמציה - עמודה מפוצלת).
5. מחק עמודות מיותרות ושנה את שמות הכותרות של הכותרות הנותרות לחשיפה טובה יותר.

כתוצאה מכך, אנו אמורים לקבל את התמונה הבאה, הרבה יותר נעימה:

נותר להעלות את הטבלה האצילית הזו בחזרה לגיליון בקובץ שלנו Handler.xlsx הקבוצה לסגור ולהוריד (בית - סגור וטען) כרטיסייה עמוד הבית:

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

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

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

הוסף טבלה חכמה עם נתיב קובץ

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

כדי ליצור טבלה חכמה מטווח רגיל, ניתן להשתמש בקיצור המקלדת Ctrl+T או כפתור עיצוב כטבלה כרטיסייה עמוד הבית (בית - עיצוב כטבלה). כותרת העמודה (תא A1) יכולה להיות כל דבר. שימו לב גם שלצורך הבהירות נתתי לטבלה שם פרמטרים כרטיסייה בנאי (לְעַצֵב).

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

אם נניח שקובץ נתוני המקור נמצא תמיד באותה תיקיה כמו המעבד שלנו, אז הנתיב שאנו צריכים יכול להיווצר על ידי הנוסחה הבאה:

=LEFT(CELL(“filename”);FIND(“[“;CELL(“filename”))-1)&”100 המוצרים המובילים.xls”

או בגרסה אנגלית:

=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Топ-100 товаров.xls»

… איפה הפונקציה LEVSIMV (שמאלה) לוקח פיסת טקסט מהקישור המלא עד לסוגריים הריבועיים הפותחים (כלומר הנתיב לתיקיה הנוכחית), ואז מודבקים אליו השם והסיומת של קובץ נתוני המקור שלנו.

קבע פרמטר של הנתיב בשאילתה

המגע האחרון והחשוב ביותר נשאר - לכתוב את הנתיב לקובץ המקור בבקשה 100 המוצרים המובילים.xls, בהתייחס לתא A2 בטבלה "החכמה" שנוצרה פרמטרים.

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

Excel.CurrentWorkbook(){[Name="Settings"]}[תוכן]0 {}[נתיב לנתוני מקור]

בוא נראה ממה זה מורכב:

• Excel.CurrentWorkbook() היא פונקציה של שפת M לגישה לתוכן הקובץ הנוכחי
• {[Name="Settings"]}[תוכן] - זהו פרמטר חידוד לפונקציה הקודמת, המציין שאנו רוצים לקבל את התוכן של הטבלה "החכמה" פרמטרים
• [נתיב לנתוני מקור] הוא שם העמודה בטבלה פרמטריםאליו אנו מתייחסים
  
• 0 {} הוא מספר השורה בטבלה פרמטריםשממנו אנחנו רוצים לקחת נתונים. המכסה לא נחשב והמספור מתחיל מאפס, לא מאחד.

זה הכל, למעשה.

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

• מהי Power Query ומדוע היא נחוצה כשעובדים ב-Microsoft Excel
• כיצד לייבא קטע טקסט צף אל Power Query
• עיצוב מחדש של XNUMXD צולב לטבלה שטוחה עם Power Query