Spojení hodnot ze všech listů ve všech souborech v konkrétním adresáři

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…

 

 

2 Komentářů

  1. 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“})

  2. Ještě oprava, ve vzorci budou samozřejmě všechny uvozovky horní.

Komentáře není možné přidávat