Spojení hodnot ze všech listů ve všech souborech v konkrétním adresáři
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
Tento článek je o tom, jak pomocí Power Query spojit data ze všech listů ze všech souborů dané složky. Souvisí s článkem, kde se řeší spojení dat ze všech souborů, a článkem, kde se řeší spojení dat ze všech listů jednoho souboru.
Jděte do rozšířeného editoru Power Query (Načíst a Transformovat) a vložte např. tuto funkci. Ta umí vybrat data ze všech listů.
(mypath)=>
let
Zdroj = Excel.Workbook(File.Contents(mypath), null, true),
#"Result" = Table.ExpandTableColumn(Zdroj, "Data", {"Column1"}, {"Column1"})
in
#"Result"
Pak tuto funkci pojmenujeme třeba MyFunction.
Funkci zavřeme a vytvoříme standardní dotaz ze složky, který vytvoří seznam všech souborů ve složce.
Jdeme na úpravy dotazu a přidáme tento nový sloupec. Tím pustíme naši vlastní funkci s názvem "MyFunction", která vytahuje data ze všech listů, na všechny soubory.
=MyFunction([Folder Path]&[Name])
Pozor, název funkce je (stejně jako většina jazyka M, který se používá v Power Query), citlivý na velká a malá písmena.
Do dotazu přibyl nový sloupec, ve kterém rozklikneme dvojitou šipku.
Hotovo.
Následně je možné v dotazu ještě odstranit nadbytečné sloupce. Stačí jen informace o tom, z jakého listu a z jakého souboru tabulka přišla. Sloupců může být samozřejmě více než jeden...
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
Neškodilo by v textu výslovně uvést, že zmíněná funkce vybere pouze první sloupec dat z každého listu. Je to sice červeně orámované v obrázku, ale méně pozornému studentu se to nemusí spojit se zápisem funkce. Když má ukázková data o více sloupcích, tak se může dlouho snažit pochopit, pro nedostane očekávaný výsledek. Pro vybrání např. prvních tří sloupců z listů bude úsek funkce vypadat asi takto:
#”Result” = Table.ExpandTableColumn(Zdroj, “Data”, {“Column1”, “Column2”, “Column3”}, {“Column1”, “Column2”, “Column3”})
Ještě oprava, ve vzorci budou samozřejmě všechny uvozovky horní.