Blog Post

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

,

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”

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating