Forum Discussion
Solved
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...
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!