Power Query-da ma'lumotlar yo'llarini parametrlash

Agar siz Microsoft Excel-da bepul Power Query plaginining vositalaridan allaqachon foydalanishni boshlagan bo'lsangiz, tez orada siz manba ma'lumotlariga havolalarni doimiy ravishda buzish bilan bog'liq bo'lgan juda ixtisoslashgan, ammo juda tez-tez va zerikarli muammoga duch kelasiz. Muammoning mohiyati shundaki, agar siz so'rovingizda tashqi fayllar yoki papkalarga murojaat qilsangiz, Power Query so'rov matnida ularga mutlaq yo'lni qattiq kodlaydi. Sizning kompyuteringizda hamma narsa yaxshi ishlaydi, lekin agar siz hamkasblaringizga so'rov bilan fayl yuborishga qaror qilsangiz, ular hafsalasi pir bo'ladi, chunki. ularning kompyuteridagi manba ma'lumotlariga boshqa yo'l bor va bizning so'rovimiz ishlamaydi.

Bunday vaziyatda nima qilish kerak? Keling, ushbu ishni quyidagi misol bilan batafsil ko'rib chiqaylik.

Muammoni shakllantirish

Aytaylik, bizda papka bor E:Savdo bo'yicha hisobotlar fayl yotadi Eng yaxshi 100 ta mahsulot.xls, bu bizning korporativ ma'lumotlar bazasidan yoki ERP tizimimizdan (1C, SAP va boshqalar) yuklangan. Ushbu fayl eng mashhur tovarlar haqidagi ma'lumotlarni o'z ichiga oladi va uning ichida quyidagicha ko'rinadi:

Excelda bu shaklda u bilan ishlash deyarli mumkin emasligi aniq bo'lsa kerak: ma'lumotlarga ega bo'sh qatorlar, birlashtirilgan hujayralar, qo'shimcha ustunlar, ko'p darajali sarlavha va boshqalar xalaqit beradi.

Shuning uchun, xuddi shu papkadagi ushbu faylning yonida biz boshqa yangi fayl yaratamiz Handler.xlsx, unda biz manba yuklash faylidan yomon ma'lumotlarni yuklaydigan Power Query so'rovini yaratamiz Eng yaxshi 100 ta mahsulot.xls, va ularni tartibga soling:

Tashqi faylga so'rov yuborish

Faylni ochish Handler.xlsx, yorliqda tanlang ma'lumotlar buyruq Ma'lumotlarni oling - Fayldan - Excel ish kitobidan (Ma'lumotlar - Ma'lumotlarni olish - Fayldan - Exceldan), keyin manba faylning joylashishini va bizga kerak bo'lgan varaqni belgilang. Tanlangan ma'lumotlar Power Query muharririga yuklanadi:

Keling, ularni normal holatga keltiraylik:

1. bilan bo'sh satrlarni o'chiring Bosh sahifa — Chiziqlarni oʻchirish — Boʻsh satrlarni oʻchirish (Uy - Qatorlarni olib tashlash - Bo'sh qatorlarni olib tashlash).
2. Keraksiz yuqori 4 qatorni o'chiring Bosh sahifa — Qatorlarni oʻchirish — Yuqori qatorlarni oʻchirish (Uy - Qatorlarni olib tashlash - Yuqori qatorlarni olib tashlash).
3. Birinchi qatorni tugma bilan jadval sarlavhasiga ko'taring Sarlavha sifatida birinchi qatordan foydalaning tab Bosh sahifa (Uy - sarlavha sifatida birinchi qatordan foydalaning).
4. Buyruq yordamida ikkinchi ustundagi mahsulot nomidan besh xonali maqolani ajrating bo'lingan ustun tab O'tkazish (O'zgartirish - Ustunni ajratish).
5. Keraksiz ustunlarni o'chiring va yaxshiroq ko'rinish uchun qolganlarning sarlavhalarini o'zgartiring.

Natijada, biz quyidagi, juda yoqimli rasmni olishimiz kerak:

Ushbu ajoyib jadvalni faylimizdagi varaqqa yuklash qoladi Handler.xlsx jamoa yoping va yuklab oling (Uy — Yopish va yuklash) tab Bosh sahifa:

So'rovdagi faylga yo'lni topish

Endi keling, bizning so'rovimiz Power Query-ga qisqacha "M" nomi bilan o'rnatilgan ichki tilda "qopqoq ostida" qanday ko'rinishini ko'rib chiqamiz. Buning uchun o'ng panelda ikki marta bosish orqali so'rovimizga qayting So'rovlar va ulanishlar va yorliqda Review tanlash Murakkab muharrir (Koʻrish — Kengaytirilgan muharrir):

Ochilgan oynada, ikkinchi qator darhol bizning asl yuklash faylimizga qattiq kodlangan yo'lni ochib beradi. Agar biz ushbu matn qatorini parametr, o'zgaruvchi yoki bu yo'l oldindan yozilgan Excel varaq katakchasiga havola bilan almashtira olsak, keyin uni osongina o'zgartirishimiz mumkin.

Fayl yo'li bilan aqlli jadval qo'shing

Hozircha Power Query-ni yopamiz va faylimizga qaytaylik Handler.xlsx. Keling, yangi bo'sh varaq qo'shamiz va unga kichik "aqlli" jadval tuzamiz, uning yagona katakchasida bizning manba ma'lumotlar faylimizga to'liq yo'l yoziladi:

Oddiy diapazondan aqlli jadval yaratish uchun siz klaviatura yorliqlaridan foydalanishingiz mumkin Ctrl+T yoki tugma Jadval sifatida formatlash tab Bosh sahifa (Uy — Jadval sifatida formatlash). Ustun sarlavhasi (A1 katak) mutlaqo har qanday bo'lishi mumkin. Shuni ham yodda tutingki, aniqlik uchun men jadvalga nom berdim Parameters tab konstruktor (Dizayn).

Yo'lni Explorer-dan nusxalash yoki hatto uni qo'lda kiritish, albatta, unchalik qiyin emas, lekin inson omilini minimallashtirish va iloji bo'lsa, yo'lni avtomatik ravishda aniqlash yaxshidir. Bu standart Excel ish varag'i funktsiyasi yordamida amalga oshirilishi mumkin JONIY (hujayra), bu argument sifatida ko'rsatilgan katak haqida bir qator foydali ma'lumotlarni, shu jumladan joriy faylga yo'lni berishi mumkin:

Agar manba ma'lumotlar fayli har doim protsessorimiz bilan bir xil papkada joylashgan deb hisoblasak, bizga kerak bo'lgan yo'lni quyidagi formula bo'yicha shakllantirish mumkin:

=CHAL(HUYAYA(“fayl nomi”);TOPISH(“[“;CELL(“fayl nomi”))-1)&”Eng yaxshi 100 ta mahsulot.xls”

yoki inglizcha versiyada:

=CHP(CELL(«fayl nomi»);TOPISH(«[«;CELL(«fayl nomi»))-1)&»Top-100 tovarov.xls»

… funksiya qayerda LEVSIMV (SOL) to'liq havoladan ochiladigan kvadrat qavsgacha (ya'ni joriy jildga yo'l) matn qismini oladi, so'ngra bizning manba ma'lumotlar faylimiz nomi va kengaytmasi unga yopishtiriladi.

So'rovdagi yo'lni parametrlashtiring

Oxirgi va eng muhim teginish qoladi - so'rovda manba fayliga yo'lni yozish Eng yaxshi 100 ta mahsulot.xls, bizning yaratilgan "aqlli" jadvalimizning A2 katagiga ishora qiladi Parameters.

Buning uchun Power Query so'roviga qaytaylik va uni qayta ochamiz Murakkab muharrir tab Review (Koʻrish — Kengaytirilgan muharrir). Qo'shtirnoq ichidagi matn qatori o'rniga “E: Savdo hisobotlari 100 ta mahsulot.xlsx” Keling, quyidagi tuzilmani kiritamiz:

Excel.CurrentWorkbook(){[Name="Sozlamalar"]}[Kontent]0 {}[Manba maʼlumotlariga yoʻl]

Keling, u nimadan iboratligini ko'rib chiqaylik:

• Excel.CurrentWorkbook() joriy fayl tarkibiga kirish uchun M tilining funksiyasi
• {[Name="Sozlamalar"]}[Kontent] - bu oldingi funktsiyani yaxshilash parametri bo'lib, biz "aqlli" jadval tarkibini olishni xohlayotganimizni ko'rsatadi. Parameters
• [Manba maʼlumotlariga yoʻl] - jadvaldagi ustun nomi Parametersbiz havola qilamiz
  
• 0 {} - jadvaldagi qator raqami Parametersbiz ma'lumot olishni xohlaymiz. Qopqoq hisoblanmaydi va raqamlash birdan emas, noldan boshlanadi.

Hammasi shu, aslida.

Bosish qoladi tugatmoq va bizning so'rovimiz qanday ishlashini tekshiring. Endi ikkala fayl bilan butun papkani boshqa shaxsiy kompyuterga yuborganda, so'rov ishlashda qoladi va ma'lumotlarga yo'lni avtomatik ravishda aniqlaydi.

• Power Query nima va u Microsoft Excelda ishlashda nima uchun kerak
• Power Query-ga suzuvchi matn parchasini qanday import qilish mumkin
• XNUMXD o'zaro faoliyat jadvalni Power Query bilan tekis jadvalga qayta loyihalash