Parametrizace datových cest v Power Query

Pokud jste již začali používat nástroje bezplatného doplňku Power Query v Microsoft Excelu, pak velmi brzy narazíte na jeden vysoce specializovaný, ale velmi častý a nepříjemný problém spojený s neustálým lámáním odkazů na zdrojová data. Podstatou problému je, že pokud ve svém dotazu odkazujete na externí soubory nebo složky, Power Query k nim pevně zakóduje absolutní cestu v textu dotazu. Na vašem počítači vše funguje dobře, ale pokud se rozhodnete poslat soubor s žádostí svým kolegům, pak budou zklamáni, protože. mají na svém počítači jinou cestu ke zdrojovým datům a náš dotaz nebude fungovat.

Co v takové situaci dělat? Podívejme se na tento případ podrobněji s následujícím příkladem.

Formulace problému

Předpokládejme, že máme ve složce E:Zprávy o prodeji leží soubor Top 100 produktů.xls, což je upload z naší podnikové databáze nebo ERP systému (1C, SAP atd.) Tento soubor obsahuje informace o nejoblíbenějších komoditních položkách a uvnitř vypadá takto:

Asi je hned od začátku jasné, že v této podobě se s tím v Excelu téměř nedá pracovat: prázdné řádky přes jeden s daty, sloučené buňky, sloupce navíc, víceúrovňové záhlaví atd. budou překážet.

Proto vedle tohoto souboru ve stejné složce vytvoříme další nový soubor Handler.xlsx, ve kterém vytvoříme dotaz Power Query, který načte ošklivá data ze zdrojového nahrávaného souboru Top 100 produktů.xlsa seřaďte je do pořadí:

Odeslání požadavku na externí soubor

Otevření souboru Handler.xlsx, vyberte na kartě Data Příkaz Získat data – ze souboru – ze sešitu aplikace Excel (Data — Získat data — Ze souboru — Z Excelu), poté zadejte umístění zdrojového souboru a listu, který potřebujeme. Vybraná data se načtou do editoru Power Query:

Pojďme je vrátit do normálu:

1. Odstraňte prázdné řádky pomocí Domů — Odstranit řádky — Odstranit prázdné řádky (Domů — Odebrat řádky — Odebrat prázdné řádky).
2. Smažte nepotřebné horní 4 řádky Domů — Odstranit řádky — Odstranit první řádky (Domů — Odebrat řádky — Odebrat horní řádky).
3. Zvedněte první řádek do záhlaví tabulky pomocí tlačítka Použijte první řádek jako záhlaví Karta Domů (Domů — Použijte první řádek jako záhlaví).
4. Pomocí příkazu oddělte pětimístný článek od názvu produktu ve druhém sloupci rozdělený sloupec Karta Proměna (Transformace – rozdělený sloupec).
5. Odstraňte nepotřebné sloupce a přejmenujte nadpisy zbývajících pro lepší viditelnost.

V důsledku toho bychom měli získat následující, mnohem příjemnější obrázek:

Zbývá nahrát tuto zušlechtěnou tabulku zpět na list v našem souboru Handler.xlsx tým zavřít a stáhnout (Domů — Zavřít a načíst) Karta Domů:

Nalezení cesty k souboru v požadavku

Nyní se podívejme, jak náš dotaz vypadá „pod kapotou“, v interním jazyce zabudovaném do Power Query s výstižným názvem „M“. Chcete-li to provést, vraťte se k našemu dotazu dvojitým kliknutím na něj v pravém podokně Požadavky a spojení a na kartě přezkoumání vybrat Pokročilý editor (Zobrazit — Pokročilý editor):

V okně, které se otevře, druhý řádek okamžitě odhaluje pevně zakódovanou cestu k našemu původnímu nahrávanému souboru. Pokud můžeme tento textový řetězec nahradit parametrem, proměnnou nebo odkazem na buňku listu Excelu, kde je tato cesta předem zapsána, můžeme to později snadno změnit.

Přidejte inteligentní tabulku s cestou k souboru

Zavřeme Power Query a vraťme se k našemu souboru Handler.xlsx. Přidáme nový prázdný list a vytvoříme na něm malou „chytrou“ tabulku, v jejíž jediné buňce bude zapsána úplná cesta k našemu zdrojovému datovému souboru:

Chcete-li vytvořit inteligentní tabulku z běžného rozsahu, můžete použít klávesovou zkratku Ctrl+T nebo tlačítko Formátovat jako tabulku Karta Domů (Domů — Formátovat jako tabulku). Záhlaví sloupce (buňka A1) může být naprosto cokoliv. Všimněte si také, že pro přehlednost jsem tabulku pojmenoval parametry Karta Stavitel (Design).

Kopírování cesty z Průzkumníka nebo dokonce její ruční zadání samozřejmě není nijak zvlášť obtížné, ale nejlepší je minimalizovat lidský faktor a cestu určovat pokud možno automaticky. To lze implementovat pomocí standardní funkce listu aplikace Excel BUŇKA (BUŇKA), který může poskytnout spoustu užitečných informací o buňce zadané jako argument – ​​včetně cesty k aktuálnímu souboru:

Pokud předpokládáme, že zdrojový datový soubor leží vždy ve stejné složce jako náš procesor, pak cestu, kterou potřebujeme, může být tvořena následujícím vzorcem:

=LEFT(BUŇKA(“název souboru”);NAJÍT(“[“;BUŇKA(“název souboru”))-1)&”100 nejlepších produktů.xls”

nebo v anglické verzi:

=LEFT(CELL(«název souboru»);NAJÍT(«[«;CELL(«název souboru»))-1)&»Топ-100 товаров.xls»

… kde je funkce LEVSIMV (VLEVO, ODJET) vezme kus textu od úplného odkazu až po úvodní hranatou závorku (tj. cestu k aktuální složce) a pak se k němu přilepí název a přípona našeho zdrojového datového souboru.

Parametrizujte cestu v dotazu

Zbývá poslední a nejdůležitější dotek – napsat do požadavku cestu ke zdrojovému souboru Top 100 produktů.xlss odkazem na buňku A2 naší vytvořené „chytré“ tabulky parametry.

Chcete-li to provést, vraťte se k dotazu Power Query a znovu jej otevřete Pokročilý editor Karta přezkoumání (Zobrazit — Pokročilý editor). Místo textového řetězce-cesta v uvozovkách “E:Zprávy o prodejiTop 100 products.xlsx” Představme si následující strukturu:

Excel.CurrentWorkbook(){[Name=”Nastavení”]}[Obsah]0 {}[Cesta ke zdrojovým datům]

Podívejme se, z čeho se skládá:

• Excel.CurrentWorkbook() je funkcí jazyka M pro přístup k obsahu aktuálního souboru
• {[Name=”Nastavení”]}[Obsah] – toto je upřesňující parametr k předchozí funkci, což znamená, že chceme získat obsah „chytré“ tabulky parametry
• [Cesta ke zdrojovým datům] je název sloupce v tabulce parametryna který odkazujeme
  
• 0 {} je číslo řádku v tabulce parametryze kterého chceme brát data. Čepice se nepočítá a číslování začíná od nuly, nikoli od jedničky.

To je vlastně všechno.

Zbývá kliknout úprava a zkontrolujte, jak náš požadavek funguje. Nyní, když posíláte celou složku s oběma soubory uvnitř do jiného PC, požadavek zůstane funkční a určí cestu k datům automaticky.

• Co je Power Query a proč je potřeba při práci v aplikaci Microsoft Excel
• Jak importovat fragment plovoucího textu do Power Query
• Přepracování křížové tabulky XNUMXD na rovnou tabulku pomocí Power Query