Parametriranje puteva podataka u Power Queryju

Ako ste već počeli koristiti alate besplatnog Power Query dodatka u programu Microsoft Excel, vrlo brzo ćete se susresti s jednim visoko specijaliziranim, ali vrlo čestim i dosadnim problemom povezanim s neprestanim prekidom veza prema izvornim podacima. Suština problema je u tome što ako u svom upitu upućujete na eksterne datoteke ili fascikle, onda Power Query čvrsto kodira apsolutnu putanju do njih u tekstu upita. Na vašem računaru sve radi dobro, ali ako odlučite da pošaljete fajl sa zahtjevom svojim kolegama, onda će oni biti razočarani, jer. oni imaju drugačiji put do izvornih podataka na svom računaru i naš upit neće raditi.

Šta učiniti u takvoj situaciji? Pogledajmo ovaj slučaj detaljnije na sljedećem primjeru.

Formulacija problema

Pretpostavimo da imamo u folderu E:Izvještaji o prodaji leži fajl Top 100 proizvoda.xls, što je otpremanje iz naše korporativne baze podataka ili ERP sistema (1C, SAP, itd.) Ova datoteka sadrži informacije o najpopularnijim robnim artiklima i izgleda ovako:

Verovatno je odmah odmah jasno da je skoro nemoguće raditi s njim u Excelu u ovom obliku: prazni redovi kroz jedan sa podacima, spojene ćelije, dodatne kolone, zaglavlje na više nivoa, itd. će ometati.

Stoga, pored ove datoteke u istoj fascikli, kreiramo još jednu novu datoteku Handler.xlsx, u kojem ćemo kreirati Power Query upit koji će učitavati ružne podatke iz izvorne datoteke za otpremanje Top 100 proizvoda.xls, i poređaj ih:

Izrada zahtjeva za eksternu datoteku

Otvaranje datoteke Handler.xlsx, odaberite na kartici podaci naredba Preuzmite podatke – iz datoteke – iz Excel radne knjige (Podaci — Uzmi podatke — Iz datoteke — Iz Excela), zatim odredite lokaciju izvorne datoteke i lista koji nam je potreban. Odabrani podaci će biti učitani u Power Query editor:

Vratimo ih u normalu:

1. Izbrišite prazne redove sa Početna — Izbriši linije — Izbrišite prazne redove (Početna — Ukloni redove — Ukloni prazne redove).
2. Izbrišite nepotrebna gornja 4 reda Početna — Izbriši redove — Izbriši gornje redove (Početna — Ukloni redove — Ukloni gornje redove).
3. Podignite prvi red do zaglavlja tabele pomoću dugmeta Koristite prvi red kao zaglavlja tabulator Početna (Početna — Koristite prvi red kao zaglavlje).
4. Odvojite petocifreni članak od naziva proizvoda u drugoj koloni pomoću naredbe split column tabulator transformacija (Transformacija — podijeljena kolona).
5. Izbrišite nepotrebne kolone i preimenujte naslove preostalih radi bolje vidljivosti.

Kao rezultat, trebali bismo dobiti sljedeću, mnogo ugodniju sliku:

Ostaje da ovu oplemenjenu tabelu prenesemo nazad na list u našem fajlu Handler.xlsx tim zatvorite i preuzmite (Početna — Zatvori&Učitaj) tabulator Početna:

Pronalaženje putanje do datoteke u zahtjevu

Sada da vidimo kako naš upit izgleda “ispod haube”, na internom jeziku ugrađenom u Power Query sa sažetim imenom “M”. Da biste to učinili, vratite se na naš upit dvostrukim klikom na njega u desnom oknu Zahtjevi i veze i na kartici pregled izabrati Napredni uređivač (Prikaz — Napredni uređivač):

U prozoru koji se otvori, drugi red odmah otkriva tvrdo kodiranu putanju do naše originalne datoteke za otpremanje. Ako možemo zamijeniti ovaj tekstualni niz parametrom, promjenljivom ili vezom do ćelije Excel lista gdje je ova putanja unaprijed napisana, možemo je kasnije lako promijeniti.

Dodajte pametnu tabelu sa putanjom datoteke

Zatvorimo Power Query za sada i vratimo se na naš fajl Handler.xlsx. Dodajmo novi prazan list i na njemu napravimo malu „pametnu“ tabelu u čiju jedinu ćeliju će biti upisana puna putanja do naše izvorne datoteke:

Da biste kreirali pametnu tabelu iz redovnog opsega, možete koristiti prečicu na tastaturi Ctrl+T ili dugme Formatirajte kao tabelu tabulator Početna (Početna — Format kao tabela). Naslov kolone (ćelija A1) može biti apsolutno bilo šta. Također imajte na umu da sam radi jasnoće dao ime tablici parametri tabulator konstruktor (Dizajn).

Kopiranje putanje iz Explorera ili čak ručno unošenje u nju, naravno, nije posebno teško, ali je najbolje minimizirati ljudski faktor i odrediti putanju, ako je moguće, automatski. Ovo se može implementirati pomoću standardne funkcije Excel radnog lista CELL (ĆELIJA), koji može dati gomilu korisnih informacija o ćeliji navedenoj kao argument – ​​uključujući putanju do trenutnog fajla:

Ako pretpostavimo da izvorna datoteka podataka uvijek leži u istoj mapi kao i naš procesor, tada se putanja koja nam je potrebna može formirati sljedećom formulom:

=LEFT(CELL(“filename”);FIND(“[“;CELL(“filename”))-1)&”Top 100 products.xls”

ili u engleskoj verziji:

=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Top-100 tovarov.xls»

… gdje je funkcija LEVSIMV (LIJEVO) uzima dio teksta od pune veze do početne uglaste zagrade (tj. putanje do trenutnog foldera), a zatim se na njega lijepi naziv i ekstenzija naše izvorne datoteke podataka.

Parametrizovati putanju u upitu

Ostaje posljednji i najvažniji dodir – upisati putanju do izvorne datoteke u zahtjevu Top 100 proizvoda.xls, koji se odnosi na ćeliju A2 naše kreirane „pametne“ tablice parametri.

Da bismo to uradili, vratimo se na Power Query upit i ponovo ga otvorimo Napredni uređivač tabulator pregled (Prikaz — Napredni uređivač). Umjesto tekstualnog niza-puta u navodnicima “E: Izvještaji o prodaji Top 100 proizvoda.xlsx” Hajde da predstavimo sledeću strukturu:

Excel.CurrentWorkbook(){[Name=”Postavke”]}[Sadržaj]0 {}[Put do izvornih podataka]

Pogledajmo od čega se sastoji:

• Excel.CurrentWorkbook() je funkcija M jezika za pristup sadržaju tekuće datoteke
• {[Name=”Postavke”]}[Sadržaj] – ovo je parametar preciziranja prethodne funkcije, koji pokazuje da želimo dobiti sadržaj „pametne“ tablice parametri
• [Put do izvornih podataka] je naziv kolone u tabeli parametrina koje se pozivamo
  
• 0 {} je broj reda u tabeli parametriiz koje želimo da uzmemo podatke. Ograničenje se ne računa i numerisanje počinje od nule, a ne od jedan.

To je sve, u stvari.

Ostaje kliknuti na završiti i provjerite kako funkcionira naš zahtjev. Sada, kada pošaljete čitav folder sa oba fajla na drugi računar, zahtev će ostati operativan i automatski će odrediti putanju do podataka.

• Šta je Power Query i zašto je potreban kada radite u Microsoft Excel-u
• Kako uvesti plutajući isječak teksta u Power Query
• Redizajniranje XNUMXD unakrsne tabele u ravnu tabelu sa Power Queryjem