Sestavte tabulky s různými záhlavími z více knih

Formulace problému

Máme několik souborů (v našem příkladu – 4 kusy, v obecném případě – tolik, kolik chcete) v jedné složce zprávy:

Uvnitř tyto soubory vypadají takto:

Kde:

• Datový list, který potřebujeme, je vždy tzv Fotky, ale může být kdekoli v sešitu.
  
• Mimo list Fotky Každá kniha může mít jiné listy.
• Tabulky s daty mají různý počet řádků a mohou začínat jiným řádkem na listu.
• Názvy stejných sloupců v různých tabulkách se mohou lišit (např. Množství = množství = množství).
• Sloupce v tabulkách mohou být uspořádány v jiném pořadí.

Úkol: shromáždit data o prodeji ze všech souborů z listu Fotky do jedné společné tabulky, abyste na ní následně sestavili souhrn nebo jakoukoli jinou analýzu.

Krok 1. Příprava adresáře názvů sloupců

První věc, kterou musíte udělat, je připravit referenční knihu se všemi možnými možnostmi pro názvy sloupců a jejich správnou interpretaci:

Tento seznam převedeme na dynamickou „chytrou“ tabulku pomocí tlačítka Formátovat jako tabulku na kartě Domů (Domů — Formátovat jako tabulku) nebo klávesovou zkratkou Ctrl+T a načtěte jej do Power Query pomocí příkazu Data – z tabulky/rozsahu (Data – z tabulky/rozsahu). V posledních verzích Excelu byl přejmenován na S listy (Z listu).

V okně editoru dotazů Power Query tradičně odstraníme krok Změněný typ a přidejte místo něj nový krok kliknutím na tlačítko f_xna řádku vzorců (pokud není vidět, můžete jej povolit na kartě přezkoumání) a zadejte tam vzorec ve vestavěném jazyce Power Query M:

=Table.ToRows(zdroj)

Tento příkaz převede příkaz načtený v předchozím kroku Zdroj referenční tabulky do seznamu sestávajícího z vnořených seznamů (List), z nichž každý je zase dvojicí hodnot Bylo-stalo z jednoho řádku:

Tento typ dat budeme potřebovat o něco později, při hromadném přejmenování hlaviček ze všech načtených tabulek.

Po dokončení převodu vyberte příkazy Domů — Zavřít a načíst — Zavřít a načíst v… a druh importu Stačí vytvořit spojení (Domů — Zavřít&Načíst — Zavřít&Načíst do… — Vytvořit pouze připojení) a vraťte se do Excelu.

Krok 2. Načteme vše ze všech souborů tak, jak je

Nyní načteme obsah všech našich souborů ze složky – prozatím tak, jak je. Výběr týmů Data – Získat data – Ze souboru – Ze složky (Data — Získat data — Ze souboru — Ze složky) a pak složku, kde jsou naše zdrojové knihy.

V okně náhledu klikněte na Konvertovat (Přeměnit) or Přeměna (Edit):

A poté rozbalte obsah všech stažených souborů (binární) tlačítko s dvojitými šipkami v záhlaví sloupce Obsah:

Power Query na příkladu prvního souboru (Vostok.xlsx) se nás zeptá na název listu, který si chceme vzít z každého sešitu – vyberte si Fotky a stiskněte OK:

Poté (ve skutečnosti) dojde k několika událostem, které nejsou pro uživatele zřejmé, jejichž důsledky jsou jasně viditelné v levém panelu:

1. Power Query vezme první soubor ze složky (budeme ho mít Vostok.xlsx — vidět Příklad souboru) jako příklad a importuje jeho obsah vytvořením dotazu Převést ukázkový soubor. Tento dotaz bude mít několik jednoduchých kroků, jako např Zdroj (přístup k souboru) Navigace (výběr listu) a případně zvýšení titulků. Tento požadavek může načíst data pouze z jednoho konkrétního souboru Vostok.xlsx.
   
2. Na základě tohoto požadavku bude vytvořena funkce s ním spojená Převést soubor (označeno charakteristickou ikonou f_x), kde zdrojovým souborem již nebude konstanta, ale proměnná hodnota – parametr. Tato funkce tedy dokáže extrahovat data z jakékoli knihy, kterou do ní vložíme jako argument.
   
3. Funkce bude postupně aplikována na každý soubor (binární) ze sloupce Obsah – je za to zodpovědný krok Volání vlastní funkce v našem dotazu, který přidá sloupec do seznamu souborů Převést soubor s výsledky importu z každého sešitu:

   

4. Nadbytečné sloupce jsou odstraněny.
5. Obsah vnořených tabulek se rozbalí (krok Rozšířený sloupec tabulky) – a vidíme konečné výsledky sběru dat ze všech knih:

   
   

Krok 3. Broušení

Předchozí snímek obrazovky jasně ukazuje, že přímá montáž „tak jak je“ se ukázala jako nekvalitní:

• Sloupce jsou obrácené.
• Mnoho řádků navíc (a nejen prázdných).
• Záhlaví tabulek nejsou vnímána jako záhlaví a jsou smíchána s daty.
  

Všechny tyto problémy můžete vyřešit velmi snadno – stačí upravit dotaz Convert Sample File. Všechny úpravy, které v něm provedeme, budou automaticky spadat do související funkce Převést soubor, což znamená, že budou použity později při importu dat z každého souboru.

Otevřením žádosti Převést ukázkový soubor, přidejte kroky k filtrování nepotřebných řádků (například podle sloupce Column2) a zvednutím nadpisů pomocí tlačítka Použijte první řádek jako záhlaví (Použít první řádek jako záhlaví). Stůl bude vypadat mnohem lépe.

Aby se sloupce z různých souborů později automaticky vešly pod sebe, musí se jmenovat stejně. Takové hromadné přejmenování můžete provést podle dříve vytvořeného adresáře s jedním řádkem M-kódu. Znovu stiskneme tlačítko f_x do řádku vzorců a přidejte funkci, kterou chcete změnit:

= Table.RenameColumns(#”Elevated Headers”, Headers, MissingField.Ignore)

Tato funkce přebírá tabulku z předchozího kroku Vyvýšené hlavičky a přejmenuje všechny sloupce v něm podle vnořeného vyhledávacího seznamu Titulky článků a reklam. Třetí argument MissingField.Ignore je potřeba, aby u těch nadpisů, které jsou v adresáři, ale nejsou v tabulce, nedocházelo k chybě.

To je vlastně všechno.

Vrátím se k žádosti zprávy uvidíme úplně jiný obrázek – mnohem hezčí než ten předchozí:

• Co je Power Query, Power Pivot, Power BI a proč je uživatel Excelu potřebuje
  
• Sběr dat ze všech souborů v dané složce
• Sběr dat ze všech listů knihy do jedné tabulky