Načítání sešitů ze složky - Power Query - pokročilé

Úvodem do pokročilejšího načítání sešitů ze složky

V tomto článku se podíváte, jak elegantně načítat data ze složky využitím nástroje Power Query, bez pomocných dotazů (Query), které se objeví pokud používáte pouze klikání na menu.

Nemusíte se strachovat většina věcí se provede klikáním, jen se něco málo úprav v M-kódu. Předvedu dvě možností jak provést:

• Pouze v jednom dotazu (Query)
• Využitím pomocné funkce na kterou se v druhém Query odkážete

Pro přehlednost je článek rozdělen na jednotlivé kapitoly, ať případně mohu doplnit, vylepšit:

• Zdrojová data
  • Sešity mají jeden či více listů načítáme listy pod stejným názvem
  • Sešity mají jeden či více listů budete potřebovat (první) nebo x-tý list
• Načtení využitím jednoho dotazu Query
• Načtení využitím funkce - příprava funkce
• Načtení využitím funkce - odkaz na funkci
• Další vylepšení a úpravy
• Související články
• Ke stažení

Zdrojová data

Pro vyzkoušení je potřeba mít v příslušné složce (adresáři) excelovské sešity, které chcete načíst (pro jednoduchost všechny, ale v závěru se dozvíte, že se dají načíst i některé). Sešity mohou mít i více listů:

• Sešity mají jeden či více listů načítáme listy pod stejným názvem
• Sešity mají jeden či více listů budete potřebovat (první) nebo x-tý list

Pro testování si připravte sešity do požadovaného adresáře, pro ukázky mám k dispozici disk E: a adresář Data.

Ukázka

Ať nemusíte vytvářet sami můžete si ukázkové soubory stáhnout.

• Soubor měsíc - Leden
• Soubor měsíc - Únor
• Soubor měsíc - Březen

Soubory uložte do vámi vybrané (vytvořené) složky. Případně si pak vygenerujte další měsíce...

Načtení využitím jednoho dotazu Query

Soubory ve složce (adresáři) máte, teď jen složku načíst. Pokud máte spuštěný Excel, stačí z menu Data > Načíst data > Ze souboru > Ze složky

V dialogovém okně vyberete složku a klik na Transformovat data

Dotaz v Power Query můžete vhodně pojmenovat, případně ponechat jako já Data ;)

Potřebujete přidat nový sloupec, který načte tabulky z jednotlivých listů. Tj. na pásu karet Přidání sloupce > Vlastní sloupec.

Nový název sloupce můžete nechat a vlastní vzorec sloupce vyplníte na základě potřeb:

• Chcete-li načíst všechny listy - což mnohdy nechcete, protože se načítá kompletní obsah listů tedy i pojmenované názvy atd.

Excel.Workbook([Content])

• Chcete-li načíst listy s názvem List1 - což mnohdy chcete (jen pozor pokud bude mít stejný název i tabulka jako tabulka)

Excel.Workbook([Content]){[Item="List1"]}[Data]

proto si pojistíme ať jde skutečně o list (Sheet)

Excel.Workbook([Content]){[Item="List1",Kind="Sheet"]}[Data]

• Chcete-li načíst první list bez ohledu na název - což také často chcete

Excel.Workbook([Content]){0}[Data]

Já potřebuji načíst listy, které se jmenuji List1 tak proto použiji:

Excel.Workbook([Content]){[Item="List1"]}[Data]

Protože informace v ostatních sloupcích, kromě posledního nepotřebuji, tak je odstraním, nad posledním sloupcem, pravý klid a Odebrat ostatní sloupce.

Následně již stačí "rozkliknout" u "dvojšipku" a vybrat, které sloupce chcete získat, já chci všechny, proto nechám všechny zaškrtnuty a klik na OK a máte téměř hotovo.

Jen ze všech tabulek se načetli všechny data (včetně názvu záhlaví). Tj. protože bylo ponecháno v dialogovém okně "Používat původní název jako předponu" což nechcete. Proto doporučuji raději odškrtnout!

Proto zbývá pár uprav. První řádek jako záhlaví a odstranit (odfiltrovat) název záhlaví, který v datech nepotřebujete. Tyto úpravy již nechám na vás.

Načtení využitím funkce - příprava funkce

Druhou možností je využít pomocné funkce, kdy budete mít funkci a jeden dotaz (Query). Připravíme si pomocnou funkci, která nám bude jednotlivé soubory načítat.

Proto si klasicky načtete excelovský soubor (třeba pro leden). Jak načítat soubory z Excelu jsem sepsal:

• Načíst soubor xlsy

Proto budu předpokládat, že soubor Leden-mzdy.xlsx máte načten a načítáte list pod názvem List1.

Teď jen z dotazu vytvoříte funkci. V menu Domů > Rozšířený editor, kdy se vám zobrazí dialogové okno, které bude obsahovat nějaký kód.

Toto kódu se neděste a nebojte, to že vypadá jinak než na ukázce nevadí. Důležité je že kód určitě začíná let a končí nějakým řádkem za slovem in:

let Zdroj = Excel.Workbook(File.Contents("E:\Data\Leden-mzdy.xlsx"), null, true), List1_Sheet = Zdroj{[Item="List1",Kind="Sheet"]}[Data], #"Záhlaví se zvýšenou úrovní" = Table.PromoteHeaders(List1_Sheet, [PromoteAllScalars=true]), #"Změněný typ" = Table.TransformColumnTypes(#"Záhlaví se zvýšenou úrovní",{{"Jméno", type text}, {"Plat", Int64.Type}, {"List", type text}, {"Soubor", type text}}) in #"Změněný typ"

Doplnění

Vám stačí doplnit před let řádek s názvem a parametry funkce:

let NactiSoubory=(Path,Name) =>

Na konec doplnit uzavření funkce:

in NactiSoubory

A upravit cestu z

Zdroj = Excel.Workbook(File.Contents("E:\Data\Leden-mzdy.xlsx"), null, true),

na

Zdroj = Excel.Workbook(Path & Name), null, true),

V mém případě vypadá takto: Ve vašem případě může být střed jiný, což na funkci nebude mít vliv:

let NactiSoubory=(Path,Name) => let Zdroj = Excel.Workbook(File.Contents(Path & Name), null, true), List1_Sheet = Zdroj{[Item="List1",Kind="Sheet"]}[Data], #"Záhlaví se zvýšenou úrovní" = Table.PromoteHeaders(List1_Sheet, [PromoteAllScalars=true]), #"Změněný typ" = Table.TransformColumnTypes(#"Záhlaví se zvýšenou úrovní",{{"Jméno", type text}, {"Plat", Int64.Type}, {"List", type text}, {"Soubor", type text}}) in #"Změněný typ" in NactiSoubory

Navíc můžete zkontrolovat, že dole je zelená "fajfka", že kód je napsán správně. Pokud ne zkontrolujte, zda máte správně písmenka a nic vám neschází a nepřebývá.

Klik na Hotovo a změní se Dotaz (Query) na funkci.

Teď je vhodné funkci přejmenovat. Dvojklik a pojmenujeme "Nacteni"

Máme připraveno na načítání...

Načtení využitím funkce - odkaz na funkci

Stejně jako v předchozím případě načteme složku ze které potřebujete získávat data.

Pokud máte spuštěný Excel, stačí z menu Data > Načíst data > Ze souboru > Ze složky, pokud jste v Power Query tak Domů > Nový zdroj > Soubor > Složka

V dialogovém okně vyberete složku a klik na Transformovat data

Dotaz v Power Query můžete vhodně pojmenovat, případně ponechat jako já Data, nebo Data (2) pokud zkoušíte obě varianty v jednom sešitě ;)

Potřebujete přidat nový sloupec, který načte tabulky z jednotlivých listů. Tj. na pásu karet Přidání sloupce > Vlastní sloupec.

V dialogovém okně Nový název klidně ponechte Vlastní, a Vlastní vzorec vložte:

Nacteni([Folder Path],[Name])

Kde:

• Nacteni - je název funkce, kterou jste vytvořili v předchozím kroku
• Folder Path je cesta, kterou bere z načtené tabulky viz názvy dostupných sloupců
• Name je název souboru, který se opět bere z načtené tabulky viz názvy dostupných sloupců

Odstraníte ostatní sloupce a ponechte jen poslední "Vlastní".

Klik na "dvojšipku".

Klik na OK a máte hotovo.

Další vylepšení a úpravy

V přípravě.

Související články

Možná vás bude ještě zajímat:

• Načítat data možností
• Načítat data z webu

Ke stažení

Soubor ke stažení zdarma je v přípravě. Podpořit zveřejnění můžete podporou na Patreon.

Závěrem

Narazili jste na nějaké problémy, máte tip na vylepšení nebo doplnění článku, můžete se zmínit v komentářích.

Článek byl aktualizován: 13.06.2023 22:01

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.