Nedávno jsem se potkal se situací, kdy jsem měl jako zdroj pro Power BI Excel, který vypadal jako kontingenční tabulka, kde v řádcích byly „Produkt“ a „Provozovna“ a ve sloupcích byly 3 pole a to:
- aktuál/budget
- rok
- měsíc
Chtěl jsem data upravit tak, aby s nimi umělo Power BI hezky pracovat. Proto jsem ze začátku zkoušel základní feature PowerQuery a skončilo to tím, že jsem vymýšlel nějaký work-around, jak k tomu přistoupit.
Pokud znáte elegantnější způsob, jak dosáhnout tíženého výsledku, budu rád, když dáte vědět. Já jsem k tomu přistoupil následovně.
Nejdřív jsem vymazal první řádek a použil „Fill down“ na první sloupec.
V následujícím kroku jsem potřeboval unpivotovat sloupce „aktuál/budget“, „rok“ a „měsíc“, ale funkce unpivot bohužel dokáže unpivotovat pouze jeden řádek.
Co teď s tím?
Transponoval jsem celou tabulku, čímž jsem si vyměnil řádky za sloupce.
Na první pohled tato tabulka potřebuje „Fill down“ pro sloupce „Aktuál/Budget“ a pro roky. Tak jsem tak učinil a zároveň jsem promazal celkové hodnoty u roků.
V dalším kroku jsem vhodným oddělovačem spojil první 3 sloupečky. Vytvořil jsem si tímto sloupec, který jsem následně umístil jako první sloupec tabulky. Nezapomněl jsem pak ani vymazat původní 3 sloupce.
Protože se mi už daná tabulka líbila, rozhodl jsem se ji transponovat zpět.
Vložil jsem první řádek jako záhlaví a unpivotoval všechny sloupce mimo prvních 2.
Sloupec „Attribute“ jsem zpět rozdělil do sloupečků dle zvoleného oddělovače a jako poslední krok jsem přejmenoval celé záhlaví, čímž jsem získal tabulku, kterou jsem na začátku potřeboval.
Pro zajímavost ještě níže úvadím PowerQuery M kód:
let
Source = Excel.Workbook(File.Contents(“<Cestka k souboru s Excelem.>”), null, true),
AV_Sheet = Source{[Item=”AV”,Kind=”Sheet”]}[Data],
#”Removed Top Rows” = Table.Skip(AV_Sheet,1),
#”Filled Down” = Table.FillDown(#”Removed Top Rows”,{“Column1″}),
#”Transposed Table” = Table.Transpose(#”Filled Down”),
#”Filled Down1″ = Table.FillDown(#”Transposed Table”,{“Column1”, “Column2″}),
#”Changed Type” = Table.TransformColumnTypes(#”Filled Down1″,{{“Column2″, type text}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each not Text.Contains([Column2], “Celkem”) or [Column2] = null),
#”Changed Type1″ = Table.TransformColumnTypes(#”Filtered Rows”,{{“Column2″, Int64.Type}}),
#”Inserted Merged Column” = Table.AddColumn(#”Changed Type1″, “Merged”, each Text.Combine({[Column1], “_”, Text.From([Column2], “cs-CZ”), “_”, Text.From([Column3], “cs-CZ”)}), type text),
#”Reordered Columns” = Table.ReorderColumns(#”Inserted Merged Column”,{“Merged”, “Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10”, “Column11”, “Column12”, “Column13”, “Column14”, “Column15”, “Column16”, “Column17”, “Column18”, “Column19″}),
#”Removed Columns” = Table.RemoveColumns(#”Reordered Columns”,{“Column1”, “Column2”, “Column3″}),
#”Transposed Table1″ = Table.Transpose(#”Removed Columns”),
#”Promoted Headers” = Table.PromoteHeaders(#”Transposed Table1″, [PromoteAllScalars=true]),
#”Unpivoted Other Columns” = Table.UnpivotOtherColumns(#”Promoted Headers”, {“__Produkt”, “__Provozovna”}, “Attribute”, “Value”),
#”Split Column by Delimiter” = Table.SplitColumn(#”Unpivoted Other Columns”, “Attribute”, Splitter.SplitTextByDelimiter(“_”, QuoteStyle.Csv), {“Attribute.1”, “Attribute.2”, “Attribute.3″}),
#”Changed Type2″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Attribute.1”, type text}, {“Attribute.2”, Int64.Type}, {“Attribute.3″, Int64.Type}}),
#”Renamed Columns” = Table.RenameColumns(#”Changed Type2″,{{“__Produkt”, “Produkt”}, {“__Provozovna”, “Provozovna”}, {“Attribute.1”, “Typ”}, {“Attribute.2”, “Rok”}, {“Attribute.3”, “Měsíc”}, {“Value”, “Hodnota”}})
in
#”Renamed Columns”