Forum Discussion

Jensej's avatar
Jensej
Helper V
5 years ago
Solved

Unpivot many columns

Hello there!   I need to normalize a table in Power BI but im not able to this do this on my own.      Can someone please help me step by step how to reach my goal.    Here is a link wit...
  • HotChilli's avatar
    HotChilli
    5 years ago

    OK, got the data.

     

    Here's the Advanced Editor code:  (for these types of problems, I do one Unpivot -> Split the attribute column to get column names -> Pivot)

     

    let
        Source = Excel.Workbook(File.Contents("J:\data\powerbiForum\UntitledUnp.xls"), null, true),
        Sheet2 = Source{[Name="Sheet1"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}, {"ID", Int64.Type}, {"Article_1", type text}, {"Article_2", type text}, {"Article_3", type text}, {"Article_4", type text}, {"Amount_1", Int64.Type}, {"Amount_2", Int64.Type}, {"Amount_3", Int64.Type}, {"Amount_4", Int64.Type}, {"Price_1", Currency.Type}, {"Price_2", Currency.Type}, {"Price_3", Currency.Type}, {"Price_4", Currency.Type}, {"Minutes_1", Int64.Type}, {"Minutes_2", Int64.Type}, {"Minutes_3", Int64.Type}, {"Minutes_4", Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
        #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," 0","",Replacer.ReplaceText,{"Date"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date", "ID"}, "Attribute", "Value"),
        #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "NULL")),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
        #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.1]), "Attribute.1", "Value"),
        #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Descending}, {"Article", Order.Ascending}}),
        #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute.2"})
    in
        #"Removed Columns"

     

    The column names and types might be different at your side.

     

    Good luck