Forum Discussion
Jensej
5 years agoHelper V
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...
- 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
Jensej
5 years agoHelper V
HotChilli
5 years agoCommunity Champion
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