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.