Power BI – unpivotování více než jednoho řádku

Nedávno jsem se potkal se situací, kdy jsem měl jako zdroj pro PowerBI 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 PowerBI 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é 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”