پارامترسازی مسیرهای داده در پاور کوئری

اگر قبلاً استفاده از ابزارهای افزونه رایگان Power Query را در مایکروسافت اکسل شروع کرده اید، خیلی زود با مشکل بسیار تخصصی، اما بسیار مکرر و آزاردهنده مرتبط با شکستن پیوسته پیوندها به داده های منبع مواجه خواهید شد. ماهیت مشکل این است که اگر در پرس و جو خود به فایل ها یا پوشه های خارجی مراجعه کنید، Power Query مسیر مطلق آنها را در متن پرس و جو کدگذاری می کند. همه چیز روی رایانه شما خوب کار می کند، اما اگر تصمیم دارید فایلی را با درخواست برای همکاران خود ارسال کنید، آنها ناامید خواهند شد، زیرا. آنها مسیر متفاوتی برای داده های منبع در رایانه خود دارند و درخواست ما کار نخواهد کرد.

در چنین شرایطی چه باید کرد؟ بیایید با مثال زیر به این مورد با جزئیات بیشتری نگاه کنیم.

فرمول بندی مسئله

فرض کنید در پوشه داریم E:گزارش های فروش فایل دروغ می گوید 100 محصول برتر.xls، که یک آپلود از پایگاه داده شرکتی یا سیستم ERP ما (1C، SAP و غیره) است.

احتمالاً کاملاً واضح است که کار کردن با آن در اکسل به این شکل تقریباً غیرممکن است: ردیف‌های خالی از یک ردیف با داده‌ها، سلول‌های ادغام شده، ستون‌های اضافی، هدر چند سطحی و غیره تداخل خواهند داشت.

بنابراین در کنار این فایل در همان پوشه، یک فایل جدید دیگر ایجاد می کنیم Handler.xlsx، که در آن یک کوئری Power Query ایجاد می کنیم که داده های زشت را از فایل آپلود منبع بارگذاری می کند 100 محصول برتر.xls، و آنها را به ترتیب قرار دهید:

درخواست به یک فایل خارجی

باز کردن فایل Handler.xlsxرا در برگه انتخاب کنید داده ها فرمان دریافت داده – از فایل – از کتاب کار اکسل (داده - دریافت داده - از فایل - از اکسل)، سپس محل فایل منبع و برگه مورد نیاز خود را مشخص کنید. داده های انتخاب شده در ویرایشگر Power Query بارگیری می شود:

بیایید آنها را به حالت عادی برگردانیم:

1. حذف خطوط خالی با صفحه اصلی — حذف خطوط — حذف خطوط خالی (صفحه اصلی - حذف ردیفها - حذف ردیفهای خالی).
2. 4 خط غیر ضروری را از طریق حذف کنید صفحه اصلی — حذف ردیف ها — حذف ردیف های بالا (صفحه اصلی - حذف ردیفها - حذف ردیفهای بالا).
3. با دکمه ردیف اول را تا سر جدول بالا ببرید از خط اول به عنوان سرصفحه استفاده کنید برگ صفحه اصلی (صفحه اصلی - استفاده از ردیف اول به عنوان سرصفحه).
4. با استفاده از دستور، مقاله پنج رقمی را از نام محصول در ستون دوم جدا کنید ستون تقسیم شده برگ دگرگونی (تبدیل - تقسیم ستون).
5. ستون‌های غیر ضروری را حذف کنید و عنوان‌های باقی‌مانده را برای دید بهتر تغییر نام دهید.

در نتیجه، باید تصویر زیر و بسیار دلپذیرتر را دریافت کنیم:

باقی می ماند که این جدول نجیب شده را دوباره به برگه فایل خود بارگذاری کنیم Handler.xlsx تیم ببندید و دانلود کنید (صفحه اصلی - بستن و بارگیری) برگ صفحه اصلی:

یافتن مسیر فایل در یک درخواست

اکنون بیایید ببینیم که پرس و جوی ما به زبان داخلی که در Power Query با نام مختصر "M" تعبیه شده است، "زیر سرپوش" چگونه به نظر می رسد. برای انجام این کار، با دوبار کلیک کردن بر روی آن در پنجره سمت راست، به پرس و جو خود بازگردید درخواست ها و اتصالات و روی زبانه مرور را انتخاب کنید ویرایشگر پیشرفته (مشاهده - ویرایشگر پیشرفته):

در پنجره‌ای که باز می‌شود، خط دوم بلافاصله یک مسیر کدگذاری شده به فایل آپلود اصلی ما را نشان می‌دهد. اگر بتوانیم این رشته متن را با یک پارامتر، متغیر یا پیوندی به یک سلول صفحه اکسل جایگزین کنیم که این مسیر از قبل نوشته شده است، بعداً می‌توانیم آن را به راحتی تغییر دهیم.

یک جدول هوشمند با یک مسیر فایل اضافه کنید

بیایید فعلا Power Query را ببندیم و به فایل خود بازگردیم Handler.xlsx. بیایید یک صفحه خالی جدید اضافه کنیم و یک جدول کوچک "هوشمند" روی آن بسازیم که در تنها سلول آن مسیر کامل فایل داده منبع ما نوشته می شود:

برای ایجاد یک جدول هوشمند از یک محدوده معمولی، می توانید از میانبر صفحه کلید استفاده کنید کلیدهای Ctrl+T یا دکمه قالب بندی به صورت جدول برگ صفحه اصلی (صفحه اصلی - قالب بندی به عنوان جدول). عنوان ستون (سلول A1) می تواند کاملاً هر چیزی باشد. همچنین توجه داشته باشید که برای وضوح، نام جدول را گذاشته ام پارامترهای برگ سازنده (طرح).

البته کپی کردن یک مسیر از اکسپلورر یا حتی وارد کردن دستی آن دشوار نیست، اما بهتر است عامل انسانی را به حداقل برسانید و در صورت امکان مسیر را به صورت خودکار تعیین کنید. این را می توان با استفاده از تابع استاندارد کاربرگ Excel پیاده سازی کرد سلول (سلول)، که می تواند مجموعه ای از اطلاعات مفید در مورد سلول مشخص شده به عنوان آرگومان ارائه دهد - از جمله مسیر فایل فعلی:

اگر فرض کنیم که فایل داده منبع همیشه در همان پوشه پردازشگر ما قرار دارد، مسیر مورد نیاز را می‌توان با فرمول زیر تشکیل داد:

=LEFT(CELL("نام فایل");FIND("[";CELL("نام فایل"))-1)&"100 محصول برتر.xls"

یا در نسخه انگلیسی:

=LEFT(CELL(«نام فایل»);FIND(«[«;CELL(«نام فایل»))-1)&»Топ-100 товаров.xls»

... عملکرد کجاست LEVSIMV (ترک کرد) یک تکه متن را از پیوند کامل تا براکت مربع باز (یعنی مسیر پوشه فعلی) می گیرد و سپس نام و پسوند فایل داده منبع ما به آن چسبانده می شود.

مسیر را در پرس و جو پارامتر کنید

آخرین و مهمترین لمس باقی می ماند - نوشتن مسیر فایل منبع در درخواست 100 محصول برتر.xlsبا اشاره به سلول A2 جدول "هوشمند" ایجاد شده ما پارامترهای.

برای انجام این کار، اجازه دهید به کوئری Power Query برگردیم و دوباره آن را باز کنیم ویرایشگر پیشرفته برگ مرور (مشاهده - ویرایشگر پیشرفته). به جای یک مسیر متنی در گیومه "E:گزارش فروش 100 محصول برتر.xlsx" بیایید ساختار زیر را معرفی کنیم:

Excel.CurrentWorkbook(){[Name="تنظیمات"]}[محتوا]0،XNUMX،XNUMX {}[مسیر به داده های منبع]

بیایید ببینیم از چه چیزی تشکیل شده است:

• Excel.CurrentWorkbook() تابعی از زبان M برای دسترسی به محتویات فایل جاری است
• {[Name="تنظیمات"]}[محتوا] - این یک پارامتر اصلاحی برای تابع قبلی است، که نشان می دهد ما می خواهیم محتویات جدول "هوشمند" را دریافت کنیم. پارامترهای
• [مسیر به داده های منبع] نام ستون در جدول است پارامترهایکه به آن اشاره می کنیم
  
• 0،XNUMX،XNUMX {} شماره ردیف در جدول است پارامترهایکه می خواهیم از آن داده بگیریم. سرپوش به حساب نمی آید و شماره گذاری از صفر شروع می شود نه از یک.

در واقع این همه چیز است.

باقی مانده است که روی آن کلیک کنید پایان و بررسی کنید که درخواست ما چگونه کار می کند. اکنون، هنگام ارسال کل پوشه با هر دو فایل داخل به رایانه شخصی دیگر، درخواست همچنان عملیاتی می شود و مسیر داده ها را به طور خودکار تعیین می کند.

• Power Query چیست و چرا هنگام کار در مایکروسافت اکسل به آن نیاز است
• نحوه وارد کردن یک قطعه متن شناور به Power Query
• طراحی مجدد XNUMXD Crosstab به یک میز مسطح با Power Query