Rozvržení kalendáře do tabulkového formátu pomocí Power Query

V tomto kurzu se dozvíte, jak pomocí Power Query v LuckyTemplates převést více rozvržení typu kalendáře do tabulkového formátu. Dozvíte se, jak transformovat více souborů namísto jednoho souboru ze složky. Celé video tohoto tutoriálu si můžete prohlédnout ve spodní části tohoto blogu.

Tento tutoriál vás také naučí, jak vyplnit mezery ve vašich datech a jak je sklízet v LuckyTemplates.

Obsah

• Příprava souborů a rozložení v Power Query
• Formulování výpočtů v Power Query
• Vyplnění mezer v datech
• Seskupování oddělených tabulek
• Závěr

Příprava souborů a rozložení v Power Query

Toto je typické rozvržení souborů a týdnů vodorovně a svisle. V jednotlivých souborech se počet řádků může lišit kvůli rozdílům v počtu řádků.

Nyní přejděte do Power Query a získejte soubory. Vyberte Nový zdroj a klikněte na Více. Poté klikněte na Vše a vyberte Složka.

Vyberte Parametr a stiskněte OK.

Zobrazí vám soubory ve složce. Musíte získat všechny soubory dohromady v jediném dotazu na výsledek. Kliknutím na Kombinovat a transformovat se načtou data a vytvoří se řada dotazů.

Dále vyberte libovolný soubor ve složce jako ukázkový soubor a vyberte libovolnou položku, kterou chcete spustit.

Když se podíváte na Query1, můžete vidět data z File1 a File2.

Formulování výpočtů v Power Query

Formát dat v Query1 je stále nesprávný. Můžete jej změnit pomocí dotazu Transform Sample File.

Pokud změníte formát tohoto dotazu na tabulkovou formu, bude sloužit jako plán pro všechny soubory ve složce.

Odstraňte první 2 řádky a sloupec 1.

Dále vyplňte mezery uvnitř stolu. Přejděte na kartu Transformace a vyberte Transponovat. Vyberte sloupce obsahující data a klikněte na záhlaví. Klikněte na Vyplnit a vyberte Dolů.

Sloupce s daty v řádku vzorců znamenají, že pokud je v každém týdnu různý počet řádků, výsledky v těchto sloupcích nebudou. Vymažte tedy krok Filled Down v sekci Applied Steps.

Každá skupina tabulek začíná sloupcem obsahujícím data. Musíte provést základní operace, abyste je dostali zpět do tabulkového formátu.

Přidejte ruční krok stisknutím f(x) v řádku vzorců. Poté zadejte Table.ToColumns . Tím se každý sloupec změní na seznam obsahující seznamy.

Udělejte ze seznamu tabulku a přidejte indexové číslo. Můžete změnit z 0 nebo 1.

Vyplnění mezer v datech

V datech jsou stále mezery, které je třeba vyplnit. Operace vyplnění však nefunguje pro seznamy. Tento seznam musíte převést na tabulku.

Vyberte sloupec Index a klikněte na Seskupit podle. Chcete-li změnit název sloupce, nahraďte Count toTable. Změňte počet řádků na FromColumns a přejděte na Column1, kde jsou umístěny seznamy. Poté jej převeďte do tabulky typů.

Nyní můžete vidět, že seznam se změnil na tabulku s pouze jedním sloupcem.

Nyní proveďte operaci vyplnění pro každou tabulku. Nejprve přidejte vlastní sloupec a nazvěte jej FillDown .

Musíte získat přístup k první hodnotě prvního sloupce v tabulce, takže pro vzorec použijte IF . Odkazujte na sloupec ToTable, který obsahuje tabulku. Zadejte Sloupec1 jako sloupec, na který chcete odkazovat. Dále použijte mezi nimi hodnotu null k načtení první hodnoty ze seznamu.

Poté zkontrolujte, zda se jedná o datum. Pokud ano, vyplňte tento sloupec dolů pomocí Table.Filldown. Uvnitř této funkce zadejte ToTable a Column1. Ale pokud to není datum, zadejte ToTable, abyste vrátili celou tabulku.

Tím získáte nový sloupec s vyplněnými tabulkami.

Najděte, kde jsou umístěna data sloupců, a rozdělte je do samostatných tabulek. Chcete-li to provést, musíte tabulky převést na seznam sloupců.

Za funkcemi Table.FillDown a Else v řádku vzorců odkazujte na Column1.

Tím se vaše tabulky změní na seznamy se sadou dat.

Poté seznam proměňte zpět na tabulku aktualizací sloupce indexu. Pamatujte, že místo dělení je tam, kde je pozice sloupce data. Přidejte tedy vlastní sloupec a nazvěte jej Index2 . Poté zkontrolujte, zda seznam obsahuje data.

Použijte IF a zadejte FillDown. Musíte pouze zkontrolovat první hodnotu v seznamu. Pokud je to datum, vraťte index. Pokud ne, zadejte null . Poté vyplňte tento sloupec dolů.

Seskupování oddělených tabulek

Použijte Seskupit podle ve sloupci Index2. Tím seskupí tabulky se stejnými hodnotami Index2 do jedné.

Nebudeš počítat řady. Změňte tedy počet řádků na FromColumns a přidejte sloupec FillDown. Poté změňte typ na tabulku.

Pokud na tabulku kliknete, zobrazí se v ní sloupce.

Dále musíte připojit všechny samostatné tabulky dohromady do jedné tabulky. Vytvořte ruční krok a použijte Table.Combine. Poté přejděte na sloupec Počet uvnitř.

Vyberte Sloupec1 a Sloupec2. Klikněte na Transformovat a vyberte Unpivot Other Columns. Poté se zobrazí 4 sloupce.

Odstraňte sloupec Atribut, protože není potřeba. Poté změňte názvy sloupců na Datum , Pohyby a Název . Změňte typ dat ve sloupci Datum na Datum a ve sloupci Název na Text.

Nyní jste aktualizovali transformace na ukázkový soubor transformace.

Pokud přejdete na Query1, můžete vidět, že sloupec1 tabulky nelze nalézt. Odstraňte tedy krok Změněný typ v části Použité kroky.

Nakonec změňte datové typy sloupců. Nastavte datový typ sloupce Datum na Datum. Poté nastavte datový typ sloupců Pohyby a Název na Text.

Nyní jste aktualizovali a změnili formát datového modelu na tabulkovou formu.

Závěr

V přehledu dat musíte vyplnit mezery v tabulce s daty převedením rozvržení kalendáře do tabulkové formy.

Pomocí Power Query v LuckyTemplates můžete transformovat více sloupců. To poskytne vaší zprávě správné a cenné informace ke sklizni.

Vše nejlepší,

Melissa