Řekněme, že máme soubor Excel s několika inteligentními tabulkami:
Pokud tyto tabulky načtete do Power Query standardním způsobem pomocí příkazu Data – Získat data – Ze souboru – Z knihy (Data — Získat data — Ze souboru — Ze sešitu), pak dostaneme něco takového:
Myslím, že obrázek zná mnoho uživatelů Power Query. Podobné vnořené tabulky lze vidět po zkombinování dotazů (a la VLOOKUP), seskupení (příkaz Skupina vytvořená Karta Proměna), import všech souborů z dané složky atd.
Dalším logickým krokem v této situaci je obvykle rozbalení všech vnořených tabulek najednou – pomocí tlačítka s dvojitými šipkami v záhlaví sloupce Data:
V důsledku toho získáme sestavení všech řádků ze všech tabulek do jediného celku. Vše je dobré, jednoduché a přehledné.
Nyní si představte, že do zdrojových tabulek byl přidán nový sloupec (Sleva) a/nebo byl odstraněn jeden ze stávajících (Město):
Náš požadavek po aktualizaci vrátí ne tak krásný obrázek – sleva se neobjevila a sloupec města byl prázdný, ale nezmizel:
A je snadné pochopit proč – na řádku vzorců můžete jasně vidět, že názvy rozbalených sloupců jsou pevně zakódovány v argumentech funkce Table.ExpandTableColumn jako seznamy ve složených závorkách.
Obejít tento problém je snadné. Nejprve pomocí funkce získáme názvy sloupců ze záhlaví libovolné (například první) tabulky Table.ColumnNames. Bude to vypadat takto:
Zde:
- #“Další sloupce odstraněny“ – název předchozího kroku, odkud data přebíráme
- 0 {} – číslo tabulky, ze které extrahujeme hlavičku (počítáme od nuly, tj. 0 je první tabulka)
- [Data] – název sloupce v předchozím kroku, kde jsou umístěny rozbalené tabulky
Zbývá dosadit konstrukci získanou v řádku vzorců do funkce Table.ExpandTableColumn v kroku rozšiřování tabulek namísto pevně zakódovaných seznamů. Vše by mělo nakonec vypadat takto:
To je vše. A při změně zdrojových dat již nebudou problémy s rozšiřováním vnořených tabulek.
- Vytváření víceformátových tabulek z jednoho listu v Power Query
- Vytvářejte tabulky s různými záhlavími z více souborů aplikace Excel
- Sběr dat ze všech listů knihy do jedné tabulky