Duomenų kelių parametravimas naudojant Power Query

Jei jau pradėjote naudoti nemokamo „Power Query“ priedo „Microsoft Excel“ įrankius, labai greitai susidursite su viena labai specializuota, bet labai dažna ir erzinančia problema, susijusia su nuolatiniu nuorodų į šaltinio duomenis laužymu. Problemos esmė ta, kad jei užklausoje nurodote išorinius failus ar aplankus, tada Power Query užklausos tekste užkoduoja absoliutų kelią iki jų. Jūsų kompiuteryje viskas veikia puikiai, bet jei nuspręsite kolegoms išsiųsti failą su prašymu, jie nusivils, nes. jie turi skirtingą kelią į šaltinio duomenis savo kompiuteryje, todėl mūsų užklausa neveiks.

Ką daryti tokioje situacijoje? Pažvelkime į šį atvejį išsamiau pagal šį pavyzdį.

Problemos formulavimas

Tarkime, kad mes turime aplanke E:Pardavimų ataskaitos guli failas 100 geriausių produktų.xls, kuris yra įkeltas iš mūsų įmonės duomenų bazės arba ERP sistemos (1C, SAP ir kt.) Šiame faile yra informacijos apie populiariausias prekių prekes ir jis viduje atrodo taip:

Tikriausiai iš karto aišku, kad tokia forma su juo dirbti programoje „Excel“ beveik neįmanoma: tuščios eilutės su duomenimis, sujungti langeliai, papildomi stulpeliai, kelių lygių antraštė ir pan.

Todėl šalia šio failo tame pačiame aplanke sukuriame dar vieną naują failą Handler.xlsx, kuriame sukursime Power Query užklausą, kuri įkels negražius duomenis iš šaltinio įkėlimo failo 100 geriausių produktų.xlsir sutvarkykite juos:

Užklausos pateikimas į išorinį failą

Failo atidarymas Handler.xlsx, pasirinkite skirtuke Duomenys Komanda Gaukite duomenis – iš failo – iš „Excel“ darbaknygės (Duomenys – gauti duomenis – iš failo – iš „Excel“), tada nurodykite šaltinio failo vietą ir mums reikalingą lapą. Pasirinkti duomenys bus įkelti į Power Query redaktorių:

Sugrąžinkime juos į įprastas:

1. Ištrinkite tuščias eilutes naudodami Pagrindinis puslapis — Ištrinti eilutes — Ištrinti tuščias eilutes (Pagrindinis – Pašalinti eilutes – Pašalinti tuščias eilutes).
2. Ištrinkite nereikalingas 4 viršutines eilutes Pagrindinis – Ištrinti eilutes – Ištrinti viršutines eilutes (Pagrindinis – Pašalinti eilutes – Pašalinti viršutines eilutes).
3. Mygtuku pakelkite pirmąją eilutę iki lentelės antraštės Naudokite pirmąją eilutę kaip antraštes kortelė Pagrindinis (Pagrindinis – naudokite pirmąją eilutę kaip antraštę).
4. Atskirkite penkių skaitmenų straipsnį nuo produkto pavadinimo antrajame stulpelyje naudodami komandą padalintas stulpelis kortelė Transformacija (Transformacija – padalintas stulpelis).
5. Ištrinkite nereikalingus stulpelius ir pervardykite likusių antraštes, kad būtų geriau matoma.

Dėl to turėtume gauti tokį, daug malonesnį vaizdą:

Belieka įkelti šią padidintą lentelę atgal į mūsų failo lapą Handler.xlsx komanda uždaryti ir atsisiųsti (Pagrindinis – Uždaryti ir įkelti) kortelė Pagrindinis:

Kelio į failą radimas užklausoje

Dabar pažiūrėkime, kaip mūsų užklausa atrodo „po gaubtu“ vidinėje „Power Query“ kalboje su glaustu pavadinimu „M“. Norėdami tai padaryti, grįžkite į mūsų užklausą dukart spustelėdami ją dešinėje srityje Prašymai ir ryšiai ir skirtuke apžvalga pasirinkti Išplėstinis redaktorius (Žiūrėti – išplėstinis redaktorius):

Atsidariusiame lange antroje eilutėje iškart rodomas užkoduotas kelias į mūsų pradinį įkėlimo failą. Jei galime pakeisti šią teksto eilutę parametru, kintamuoju arba nuoroda į „Excel“ lapo langelį, kuriame šis kelias yra iš anksto parašytas, vėliau galėsime jį lengvai pakeisti.

Pridėkite išmaniąją lentelę su failo keliu

Kol kas uždarykime „Power Query“ ir grįžkime prie failo Handler.xlsx. Pridėkime naują tuščią lapą ir padarykime jame nedidelę „protingą“ lentelę, kurios vieninteliame langelyje bus parašytas visas kelias į mūsų šaltinio duomenų failą:

Norėdami sukurti išmaniąją lentelę iš įprasto diapazono, galite naudoti sparčiuosius klavišus "Ctrl"+T arba mygtuką Formatuokite kaip lentelę kortelė Pagrindinis (Pagrindinis – formatuoti kaip lentelę). Stulpelio antraštė (ląstelė A1) gali būti visiškai bet kokia. Taip pat atkreipkite dėmesį, kad aiškumo dėlei pateikiau lentelės pavadinimą Parametrai kortelė Konstruktorius (Dizainas).

Nukopijuoti kelią iš Explorer ar net įvesti jį rankiniu būdu, žinoma, nėra ypač sunku, tačiau geriausia sumažinti žmogiškąjį faktorių ir nustatyti kelią, jei įmanoma, automatiškai. Tai galima įgyvendinti naudojant standartinę Excel darbalapio funkciją LĄSTELĖ (Ląstelė), kuri gali suteikti daug naudingos informacijos apie langelį, nurodytą kaip argumentą, įskaitant kelią į dabartinį failą:

Jei darysime prielaidą, kad šaltinio duomenų failas visada yra tame pačiame aplanke kaip ir mūsų procesorius, mums reikiamą kelią galima sudaryti pagal šią formulę:

=LEFT(CELL("failo pavadinimas");RASTI("[";CELL("failo pavadinimas")-1)&"100 populiariausių produktų.xls"

arba anglų kalba:

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

... kur yra funkcija LEVSIMV (Kairė) paima teksto dalį nuo visos nuorodos iki pradinio laužtinio skliausto (ty kelias į dabartinį aplanką), tada prie jo priklijuojamas šaltinio duomenų failo pavadinimas ir plėtinys.

Parametrizuokite kelią užklausoje

Lieka paskutinis ir svarbiausias prisilietimas – užklausoje įrašyti kelią į šaltinio failą 100 geriausių produktų.xls, nurodant mūsų sukurtos „išmaniosios“ lentelės langelį A2 Parametrai.

Norėdami tai padaryti, grįžkime prie Power Query užklausos ir atidarykite ją dar kartą Išplėstinis redaktorius kortelė apžvalga (Žiūrėti – išplėstinis redaktorius). Vietoj teksto eilutės kelio kabutėse „E: Pardavimo ataskaitos 100 populiariausių produktų.xlsx“ Pristatykime tokią struktūrą:

Excel.CurrentWorkbook(){[Name="Nustatymai"]}[Turinys]0 {}[Kelias į šaltinio duomenis]

Pažiūrėkime, iš ko jis susideda:

• Excel.CurrentWorkbook() yra M kalbos funkcija, skirta prieigai prie dabartinio failo turinio
• {[Name="Nustatymai"]}[Turinys] – tai ankstesnės funkcijos patikslinimo parametras, nurodantis, kad norime gauti „protingos“ lentelės turinį Parametrai
• [Kelias į šaltinio duomenis] yra lentelės stulpelio pavadinimas Parametraiį kurią mes kalbame
  
• 0 {} yra eilutės numeris lentelėje Parametraiiš kurių norime paimti duomenis. Dangtelis nesiskaito ir numeracija prasideda nuo nulio, o ne nuo vieneto.

Tai viskas, tiesą sakant.

Belieka spustelėti apdaila ir patikrinkite, kaip veikia mūsų užklausa. Dabar, siunčiant visą aplanką su abiem failais į kitą kompiuterį, užklausa veiks ir automatiškai nustatys kelią į duomenis.

• Kas yra Power Query ir kodėl ji reikalinga dirbant su Microsoft Excel
• Kaip importuoti slankiojo teksto fragmentą į „Power Query“.
• XNUMXD Crosstab pertvarkymas į plokščią lentelę su Power Query