Forum Discussion
Table transformation challenge
Hi, I am somewhat new to PowerBI. Only been using it for less than a year. I have a dataset in the format below (top). What is the best way to transform it to the format on the bottom? Appreciate all the suggestions!
Hi, BI-Beginner ;
As HotChilli said, I'll add specific steps：
1.unpivot columns.
2.split column by "#".
3.select two columns then pivot it.
4.delete Attribute.2 column and the final result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MlbSUSpPSQSSKqYgZlERiGmmFKsTreRiYmYO5CWmpIHEQCpTU1NBTAul2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Supplier Name#1" = _t, #" Supplier Cost#1" = _t, #"Supplier Name#2" = _t, #" Supplier Cost#2" = _t]), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item", type text}, {"Attribute.1", type text}, {"Attribute.2", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.1]), "Attribute.1", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"}) in #"Removed Columns"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Unpivot all columns except item.
Split the column with the numbers and then Pivot the column with Supplier Name and Supplier Cost in it.
There are plenty of examples of this in this PQ forum
Hi, BI-Beginner ;
As HotChilli said, I'll add specific steps：
1.unpivot columns.
2.split column by "#".
3.select two columns then pivot it.
4.delete Attribute.2 column and the final result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MlbSUSpPSQSSKqYgZlERiGmmFKsTreRiYmYO5CWmpIHEQCpTU1NBTAul2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Supplier Name#1" = _t, #" Supplier Cost#1" = _t, #"Supplier Name#2" = _t, #" Supplier Cost#2" = _t]), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item", type text}, {"Attribute.1", type text}, {"Attribute.2", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.1]), "Attribute.1", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"}) in #"Removed Columns"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Yalan,
Thanks for the suggestion! I ran into an issue at the last step. Can you please let me know what I might be doing wrong?
I got the data set cleaned up and made it into the follow format.
When I tried to pivot the two columns, it gave me this error.
let Source = Excel.Workbook(File.Contents("C:\Users\test\Documents\Test Power BI\Book1.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item", type text}, {"Supplier #1", type text}, {"Supplier #1 Pricing", Int64.Type}, {"Supplier #2", type text}, {"Supplier #2 Pricing", Int64.Type}, {"Supplier #3", type text}, {"Supplier #3 Pricing", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","#1","",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#2","",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","#3","",Replacer.ReplaceText,{"Attribute"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value2",{{"Value", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute]), "Attribute", "Value") in #"Pivoted Column"I figured it out... Because I didn't have an index column. Thank you for your help!
