Ako ste već počeli koristiti alate besplatnog dodatka Power Query u Microsoft Excelu, vrlo brzo ćete se susresti s jednim visoko specijaliziranim, ali vrlo čestim i neugodnim problemom povezanim sa stalnim prekidanjem veza s izvornim podacima. Suština problema je u tome što ako se u svom upitu pozivate na vanjske datoteke ili mape, onda Power Query hardcodera apsolutni put do njih u tekstu upita. Na vašem računalu sve radi dobro, ali ako odlučite poslati datoteku sa zahtjevom svojim kolegama, oni će biti razočarani, jer. imaju drugačiji put do izvornih podataka na svom računalu i naš upit neće raditi.

Što učiniti u takvoj situaciji? Pogledajmo ovaj slučaj pobliže na sljedećem primjeru.

Formulacija problema

Pretpostavimo da imamo u mapi E:Izvješća o prodaji leži datoteka 100 najboljih proizvoda.xls, koji je prijenos iz naše korporativne baze podataka ili ERP sustava (1C, SAP, itd.) Ova datoteka sadrži informacije o najpopularnijim robnim artiklima i izgleda ovako unutra:

Parametriranje putova podataka u Power Queryju

Vjerojatno je odmah jasno da je gotovo nemoguće raditi s njim u Excelu u ovom obliku: prazni redovi do jednog s podacima, spojene ćelije, dodatni stupci, zaglavlje na više razina itd. će smetati.

Stoga, pored ove datoteke u istoj mapi, kreiramo još jednu novu datoteku Rukovatelj.xlsx, u kojem ćemo izraditi Power Query upit koji će učitati ružne podatke iz izvorne upload datoteke 100 najboljih proizvoda.xls, i poredajte ih:

Parametriranje putova podataka u Power Queryju

Izrada zahtjeva za vanjsku datoteku

Otvaranje datoteke Rukovatelj.xlsx, odaberite na kartici Datum naredba Dobivanje podataka – iz datoteke – iz Excel radne knjige (Podaci — Dohvati podatke — Iz datoteke — Iz Excela), zatim odredite mjesto izvorne datoteke i lista koji nam je potreban. Odabrani podaci će se učitati u Power Query editor:

Parametriranje putova podataka u Power Queryju

Vratimo ih u normalu:

  1. Izbrišite prazne retke pomoću Početna — Izbriši retke — Izbrišite prazne retke (Početna — Ukloni retke — Ukloni prazne retke).
  2. Izbrišite nepotrebna gornja 4 retka Početna — Izbriši retke — Izbriši gornje retke (Početna — Ukloni redove — Ukloni gornje redove).
  3. Gumbom podignite prvi red do zaglavlja tablice Koristite prvi redak kao zaglavlja kartica Naslovna (Početna — koristi prvi redak kao zaglavlje).
  4. Naredbom odvojite peteroznamenkasti artikl od naziva proizvoda u drugom stupcu podijeljeni stupac kartica transformacija (Transformacija — Podijeli stupac).
  5. Izbrišite nepotrebne stupce i preimenujte naslove preostalih radi bolje preglednosti.

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

Parametriranje putova podataka u Power Queryju

Ostaje još da učitamo ovu oplemenjenu tablicu natrag na list u našoj datoteci Rukovatelj.xlsx Tim zatvorite i preuzmite (Početna — Zatvori&Učitaj) kartica Naslovna:

Parametriranje putova podataka u Power Queryju

Pronalaženje putanje do datoteke u zahtjevu

Sada da vidimo kako naš upit izgleda “ispod haube”, u internom jeziku ugrađenom u Power Query sa sažetim nazivom “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č):

Parametriranje putova podataka u Power Queryju

U prozoru koji se otvori, drugi red odmah otkriva tvrdo kodirani put do naše izvorne datoteke za učitavanje. Ako možemo zamijeniti ovaj tekstualni niz parametrom, varijablom ili vezom na ćeliju Excel lista gdje je ovaj put unaprijed napisan, tada ga kasnije možemo lako promijeniti.

Dodajte pametnu tablicu s putanjom datoteke

Za sada zatvorimo Power Query i vratimo se našoj datoteci Rukovatelj.xlsx. Dodajmo novi prazan list i na njemu napravimo malu “pametnu” tablicu u čijoj će jedinoj ćeliji biti zapisana puna staza do naše izvorne podatkovne datoteke:

Parametriranje putova podataka u Power Queryju

Da biste izradili pametnu tablicu iz uobičajenog raspona, možete upotrijebiti tipkovni prečac Ctrl+T ili gumb Formatiraj kao tablicu kartica Naslovna (Početna — Formatiraj kao tablicu). Naslov stupca (ćelija A1) može biti apsolutno bilo što. Također imajte na umu da sam radi jasnoće tablici dao ime Parametri kartica Konstruktor (Oblikovati).

Kopiranje staze iz Explorera ili čak ručno unošenje nije, naravno, osobito teško, ali najbolje je minimizirati ljudski faktor i odrediti stazu, ako je moguće, automatski. To se može implementirati korištenjem standardne funkcije Excel radnog lista CELL (ĆELIJA), koji može dati hrpu korisnih informacija o ćeliji navedenoj kao argument – ​​uključujući put do trenutne datoteke:

Parametriranje putova podataka u Power Queryju

Ako pretpostavimo da izvorna podatkovna datoteka uvijek leži u istoj mapi kao i naš procesor, tada se staza koja nam treba može oblikovati sljedećom formulom:

Parametriranje putova podataka u Power Queryju

=LEFT(CELL(“naziv datoteke”);FIND(“[“;CELL(“naziv datoteke”))-1)&”100 najboljih proizvoda.xls”

ili u engleskoj verziji:

=LEFT(CELL(«naziv datoteke»);FIND(«[«;CELL(«naziv datoteke»))-1)&»Top-100 proizvoda.xls»

… gdje je funkcija LEVSIMV (LIJEVO) uzima dio teksta od pune veze do početne uglate zagrade (tj. staze do trenutne mape), a zatim se naziv i ekstenzija naše izvorne podatkovne datoteke lijepe na njega.

Parametrirajte put u upitu

Ostaje posljednji i najvažniji dodir - napisati put do izvorne datoteke u zahtjevu 100 najboljih proizvoda.xls, pozivajući se na ćeliju A2 naše stvorene "pametne" tablice Parametri.

Da bismo to učinili, vratimo se na upit Power Query i ponovno ga otvorimo Napredni uređivač kartica pregled (Prikaz — napredni uređivač). Umjesto teksta string-path u navodnicima “E:Izvješća o prodaji 100 najboljih proizvoda.xlsx” Predstavimo sljedeću strukturu:

Parametriranje putova podataka u Power Queryju

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

Pogledajmo od čega se sastoji:

  • Excel.CurrentWorkbook() je funkcija jezika M za pristup sadržaju trenutne 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 stupca u tablici Parametrina koje se pozivamo
  • 0 {} je broj retka u tablici Parametriiz kojeg želimo uzeti podatke. Kapica se ne računa i numeriranje počinje od nule, a ne od jedinice.

To je sve, zapravo.

Ostaje kliknuti na završiti i provjerite kako funkcionira naš zahtjev. Sada, kada šaljete cijelu mapu s obje datoteke unutra na drugo računalo, zahtjev će ostati operativan i automatski će odrediti put do podataka.

  • Što je Power Query i zašto je potreban pri radu u Microsoft Excelu
  • Kako uvesti isječak plutajućeg teksta u Power Query
  • Redizajn XNUMXD unakrsne tablice u ravnu tablicu s Power Queryjem

Ostavi odgovor