Adatútvonalak paraméterezése a Power Queryben

Ha már elkezdte használni a Microsoft Excel ingyenes Power Query-bővítményének eszközeit, akkor hamarosan egy nagyon speciális, de nagyon gyakori és bosszantó problémával fog találkozni, amely a forrásadatokra mutató hivatkozások állandó feltörésével jár. A probléma lényege, hogy ha a lekérdezésben külső fájlokra vagy mappákra hivatkozik, akkor a Power Query a lekérdezés szövegében beírja az abszolút elérési utat. Minden rendben működik a számítógépén, de ha úgy dönt, hogy elküld egy fájlt egy kéréssel a kollégáinak, akkor csalódni fognak, mert. más az elérési útjuk a számítógépükön lévő forrásadatokhoz, és a lekérdezésünk nem fog működni.

Mit kell tenni ilyen helyzetben? Nézzük meg ezt az esetet részletesebben a következő példával.

A probléma megfogalmazása

Tegyük fel, hogy van a mappában E:Értékesítési jelentések fekszik a fájl Top 100 termék.xls, amely vállalati adatbázisunkból vagy ERP rendszerünkből (1C, SAP stb.) származó feltöltés. Ez a fájl a legnépszerűbb árucikkekről tartalmaz információkat, és így néz ki belülről:

Valószínűleg azonnal egyértelmű, hogy ebben a formában szinte lehetetlen vele dolgozni Excelben: az üres sorok az adatokkal, egyesített cellák, extra oszlopok, többszintű fejléc stb.

Ezért e fájl mellett ugyanabban a mappában létrehozunk egy másik új fájlt Handler.xlsx, amelyben létrehozunk egy Power Query lekérdezést, amely csúnya adatokat tölt be a forrásfeltöltési fájlból Top 100 termék.xls, és tedd őket sorrendbe:

Külső fájl kérése

A fájl megnyitása Handler.xlsx, válassza ki a lapon dátum parancs Adatok beszerzése – Fájlból – Excel-munkafüzetből (Adatok — Adatok lekérése — Fájlból — Excelből), majd adja meg a forrásfájl helyét és a szükséges lapot. A kiválasztott adatok betöltődnek a Power Query szerkesztőbe:

Állítsuk vissza őket a normál állapotba:

1. Az üres sorok törlése a következővel: Kezdőlap — Sorok törlése — Üres sorok törlése (Kezdőlap – Sorok eltávolítása – Üres sorok eltávolítása).
2. Törölje a felesleges felső 4 sort Kezdőlap — Sorok törlése — Felső sorok törlése (Kezdőlap – Sorok eltávolítása – Felső sorok eltávolítása).
3. Emelje fel az első sort a táblázat fejlécére a gombbal Használja az első sort fejlécként lap Kezdőlap (Főoldal – Az első sor használata fejlécként).
4. A paranccsal válassza el az ötjegyű cikket a terméknévtől a második oszlopban hasított oszlop lap Átalakítás (Átalakítás – Oszlop).
5. Törölje a felesleges oszlopokat, és nevezze át a fennmaradó oszlopok fejléceit a jobb láthatóság érdekében.

Ennek eredményeként a következő, sokkal kellemesebb képet kell kapnunk:

Marad hátra, hogy ezt a nemesített táblázatot visszatöltsük a fájlunkban lévő lapra Handler.xlsx a csapat zárja be és töltse le (Főoldal – Bezárás és betöltés) lap Kezdőlap:

A fájl elérési útjának megkeresése egy kérésben

Most pedig nézzük meg, hogyan néz ki a lekérdezésünk „a motorháztető alatt”, a Power Querybe épített belső nyelven, tömör „M” névvel. Ehhez térjen vissza a lekérdezésünkhöz úgy, hogy duplán kattintson rá a jobb oldali ablaktáblában Kérések és kapcsolatok és a lapon Felülvizsgálat választani Speciális szerkesztő (Nézet – Speciális szerkesztő):

A megnyíló ablakban a második sor azonnal felfedi az eredeti feltöltési fájlunk kódolt elérési útját. Ha ezt a szöveges karakterláncot le tudjuk cserélni egy paraméterrel, változóval vagy egy Excel munkalap cellájára mutató hivatkozással, ahol ez az elérési út előre meg van írva, akkor később könnyen megváltoztathatjuk.

Adjon hozzá egy intelligens táblázatot fájl elérési úttal

Egyelőre zárjuk be a Power Queryt, és térjünk vissza a fájlunkhoz Handler.xlsx. Adjunk hozzá egy új üres lapot, és készítsünk rá egy kis „okos” táblázatot, amelynek egyetlen cellájába írjuk be a forrásadatfájlunk teljes elérési útját:

Intelligens táblázat létrehozásához normál tartományból használhatja a billentyűkódot Ctrl+T vagy gombot Formázás táblázatként lap Kezdőlap (Főoldal — Táblázat formázása). Az oszlopfejléc (A1 cella) bármi lehet. Azt is vegye figyelembe, hogy az egyértelműség kedvéért nevet adtam a táblázatnak paraméterek lap építész (Tervezés).

Egy elérési út Explorerből másolása, vagy akár manuális megadása természetesen nem különösebben nehéz, de a legjobb, ha minimalizáljuk az emberi tényezőt, és ha lehetséges, automatikusan meghatározzuk az utat. Ez a szabványos Excel munkalapfüggvény segítségével valósítható meg SEJT (SEJT), amely egy csomó hasznos információt közölhet az argumentumként megadott celláról – beleértve az aktuális fájl elérési útját is:

Ha feltételezzük, hogy a forrásadatfájl mindig ugyanabban a mappában található, mint a processzorunk, akkor a szükséges elérési út a következő képlettel alakítható ki:

=LEFT(CELL("fájlnév");KERESÉS("[";CELL("fájlnév")-1)&"A 100 legnépszerűbb termék.xls"

vagy angol változatban:

=LEFT(CELL(«fájlnév»);FIND(«[«;CELL(«fájlnév»))-1)&»Топ-100 товаров.xls»

… hol a függvény LEVSIMV (BAL) átvesz egy szövegrészt a teljes hivatkozástól egészen a nyitó szögletes zárójelig (azaz az aktuális mappa elérési útja), majd ráragasztja a forrásadatfájlunk nevét és kiterjesztését.

Paraméterezze az elérési utat a lekérdezésben

Marad az utolsó és legfontosabb érintés – a forrásfájl elérési útjának beírása a kérésbe Top 100 termék.xls, a létrehozott „okos” táblázatunk A2 cellájára hivatkozva paraméterek.

Ehhez térjünk vissza a Power Query lekérdezéshez, és nyissa meg újra Speciális szerkesztő lap Felülvizsgálat (Nézet – Speciális szerkesztő). Szöveges karakterlánc-útvonal helyett idézőjelben „E: Értékesítési jelentések A 100 legjobb termék.xlsx” Mutassuk be a következő szerkezetet:

Excel.CurrentWorkbook(){[Name="Beállítások"]}[Tartalom]0 {}[A forrásadatok elérési útja]

Lássuk, miből áll:

• Excel.CurrentWorkbook() az M nyelv függvénye az aktuális fájl tartalmának eléréséhez
• {[Name="Beállítások"]}[Tartalom] – ez egy finomító paraméter az előző függvényhez, jelezve, hogy az „okos” tábla tartalmát szeretnénk megkapni paraméterek
• [A forrásadatok elérési útja] a táblázat oszlopának neve paraméterekamelyre hivatkozunk
  
• 0 {} a sor száma a táblázatban paraméterekahonnan adatokat akarunk venni. A kupak nem számít, és a számozás nullától kezdődik, nem egytől.

Valójában ennyi.

Már csak rá kell kattintani befejez és ellenőrizze, hogyan működik a kérésünk. Most, amikor a teljes mappát mindkét fájllal egy másik számítógépre küldi, a kérés működőképes marad, és automatikusan meghatározza az adatok elérési útját.

• Mi az a Power Query, és miért van rá szükség a Microsoft Excel programban végzett munka során
• Lebegő szövegrészlet importálása a Power Querybe
• XNUMXD Crosstab újratervezése lapos táblává Power Query segítségével