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“

I have been dealing with MS SQL Server for several years. I enjoy both scripting and server administration. Joyful Craftsmen allowed me to deepen my knowledge on cloud solutions as well. Therefore, I would like to share with you the acquired knowledge in the field of data migration to Azure.

LUKÁŠ KARLOVSKÝ
Data Engineer
LinkedIn

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *

Prosím, vyplňte toto pole.
Prosím, vyplňte toto pole.
Zadejte prosím platnou e-mailovou adresu.
Chcete-li pokračovat, musíte souhlasit s podmínkami