Forum Discussion
Transform data - Unpivot and Pivot Column
Hi, I try to transform the below data to the expected result but failed. Please help.
| Campaign Name | Name | Product | Qty | Price | Product.1 | Qty.1 | Price.1 | Product.2 | Qty.2 | Price.2 | Product.3 | Qty.3 | Price.3 |
| ABC | Bobo | Apple | 1 | 16.00 | Orange | 1 | 25.80 | Grape | 2 | 20.00 | Banana | 1 | 36.45 |
| ABC | Bobo | Apple | 6 | 96.00 | Orange | 7 | 180.60 | Grape | 8 | 80.00 | Banana | 8 | 291.60 |
Expected Result:
| Campaign Name | Name | Product | Qty | Price |
| ABC | Bobo | Apple | 1 | 16.00 |
| ABC | Bobo | Orange | 1 | 25.80 |
| ABC | Bobo | Grape | 2 | 20.00 |
| ABC | Bobo | Banana | 1 | 36.45 |
| ABC | Bobo | Apple | 6 | 96.00 |
| ABC | Bobo | Orange | 7 | 180.60 |
| ABC | Bobo | Grape | 8 | 80.00 |
| ABC | Bobo | Banana | 8 | 291.60 |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcspPygdSjgUFOalA2hCEzfQMDIC0f1FiXjpM0MhUzwIk6F6UWAASMwJhA4hCp8Q8IIQqNDbTMzFVitXBYYEZEFuiW2AO0mthoGeGbIMFCKPbABI0sjQEqYyNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Campaign Name" = _t, #" Name" = _t, Product = _t, Qty = _t, Price = _t, Product.1 = _t, Qty.1 = _t, Price.1 = _t, Product.2 = _t, Qty.2 = _t, Price.2 = _t, Product.3 = _t, Qty.3 = _t, Price.3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign Name", type text}, {" Name", type text}, {"Product", type text}, {"Qty", Int64.Type}, {"Price", type text}, {"Product.1", type text}, {"Qty.1", Int64.Type}, {"Price.1", type text}, {"Product.2", type text}, {"Qty.2", Int64.Type}, {"Price.2", type text}, {"Product.3", type text}, {"Qty.3", Int64.Type}, {"Price.3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Campaign Name", " Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign Name", type text}, {" Name", type text}, {" Contact No", Int64.Type}, {" Date Created", type text}, {"Product", type text}, {"Qty", Int64.Type}, {"Price", type text}, {"Product2", type text}, {"Qty3", Int64.Type}, {"Price4", type text}, {"Product5", type text}, {"Qty6", Int64.Type}, {"Price7", type text}, {"Product8", type text}, {"Qty9", Int64.Type}, {"Price10", type text}, {"Product11", type text}, {"Qty12", Int64.Type}, {"Price13", type text}, {"Product14", type text}, {"Qty15", Int64.Type}, {"Price16", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Campaign Name", " Name", " Contact No", " Date Created", "Product", "Qty", "Price", "Product2", "Qty3", "Price4", "Product5", "Qty6", "Price7", "Product8", "Qty9", "Price10", "Product11", "Qty12", "Price13", "Product14", "Qty15", "Price16"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index", "Campaign Name", " Name", " Contact No", " Date Created"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Product", each if [Attribute]="Product" then [Value] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Product"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Attribute], "Product")), #"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows",{"Index", "Campaign Name", " Name", " Contact No", " Date Created", "Product", "Attribute", "Value"}), #"Split Column by Character Transition" = Table.SplitColumn(#"Reordered Columns1", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-IN"),{"Index", "Attribute.1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), #"Added Index1" = Table.AddIndexColumn(#"Merged Columns", "Index", 1, 1, Int64.Type), Partition = Table.Group(#"Added Index1", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Campaign Name", " Name", " Contact No", " Date Created", "Product", "Value", "Index", "Index1"}, {"Campaign Name", " Name", " Contact No", " Date Created", "Product", "Value", "Index", "Index1"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Partition", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Merged.1", "Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Merged.2]), "Merged.2", "Value"), #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"Qty", type number}}) in #"Changed Type2"Hope this helps.
7 Replies
PBI_newuser , Please try. I added index column first
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRcspPygdSjgUFOalA2hCEzfQMDIC0f1FiXjpM0MhUzwIk6F6UWAASMwJhA4hCp8Q8IIQqNDbTMzFVitXBYYEZEFuiW2AO0mthoGeGbIMFCKPbABI0sjQEqYyNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Campaign Name" = _t, #" Name" = _t, Product = _t, Qty = _t, Price = _t, Product.1 = _t, Qty.1 = _t, Price.1 = _t, Product.2 = _t, Qty.2 = _t, Price.2 = _t, Product.3 = _t, Qty.3 = _t, Price.3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign Name", type text}, {" Name", type text}, {"Product", type text}, {"Qty", Int64.Type}, {"Price", Int64.Type}, {"Product.1", type text}, {"Qty.1", Int64.Type}, {"Price.1", type number}, {"Product.2", type text}, {"Qty.2", Int64.Type}, {"Price.2", Int64.Type}, {"Product.3", type text}, {"Qty.3", Int64.Type}, {"Price.3", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Campaign Name", " Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"Hi amitchandak , when i import this excel file, the query is not working. Could you please help to see what's wrong with the query? Sample file here.
Excel data:
Campaign Name Name Contact No Date Created Product Qty Price Product Qty Price Product Qty Price Product Qty Price Product Qty Price Product Qty Price ABC Bobo 123456 March 9, 2022 (Wed) Apple 1 MYR 16.00 Orange 1 MYR 25.80 Grape 2 MYR 20.00 Plum 1 MYR 36.45 ABC Bobo 654321 March 9, 2022 (Wed) Avocado 6 MYR 96.00 Grape 7 MYR 180.60 Orange 8 MYR 80.00 Plum 8 MYR 291.60 BCD Alex 456789 March 12, 2022 (Sat) Apple 3 MYR 345.00 Banana 4 MYR 752.00 Watermelon 3 MYR 465.00 Grape 3 MYR 450.00 Mango 2 MYR 258.00 Guava 3 MYR 840.00
1) Check the columns that should not change and choose "Unpivot other Columns"
2) Check (the new) colum "Attribute" and Split by delimiter "."
3) Replace null with zero.
4) Check the new column and pivot again.
Regards
DI
Did I answer your question? Mark my post as a solution, this will help others!
Kudos are also welcome.Hi darshaningale , when i import this excel file, the query is not working. Could you please help to see what's wrong with the query? Sample file here. Thank you.
Hi PBI_newuser ,
Please try the following code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZHBasMwDIZfxeS0QQi2YjvOselgp9KyHcooOWit2Q5pHEJb+viVG7s4Y8UYC/367U/ybpctmmWWZ437dnQIKKXSFKxw3P+yOmfAAdjL1h5eKbsYhs76Ml/x9cGELjineD1i/5MKoArjhfcRB5+HmOeTYdOdj0l5qQupKGb/7KzN/0BqJUsQzyEvbo+He2G4vg6YkaaK+IYXesZvgmLmnDENtZgMT0Gb5ZtH6OyVDhplZeoHqIBI+omndJxlHINU07MN9rT8DUGpFEzKFk92PNrO9YlPajVv8CGo0MeK+nPpPygTHGe8YOIw8u5o2xs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Campaign Name" = _t, Name = _t, #"Contact No" = _t, #"Date Created" = _t, Product = _t, Qty = _t, Price = _t, Product.1 = _t, Qty.1 = _t, Price.1 = _t, Product.2 = _t, Qty.2 = _t, Price.2 = _t, Product.3 = _t, Qty.3 = _t, Price.3 = _t, Product.4 = _t, Qty.4 = _t, Price.4 = _t, Product.5 = _t, Qty.5 = _t, Price.5 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Campaign Name", type text}, {"Name", type text}, {"Contact No", Int64.Type}, {"Date Created", type text}, {"Product", type text}, {"Qty", Int64.Type}, {"Price", type text}, {"Product.1", type text}, {"Qty.1", Int64.Type}, {"Price.1", type text}, {"Product.2", type text}, {"Qty.2", Int64.Type}, {"Price.2", type text}, {"Product.3", type text}, {"Qty.3", Int64.Type}, {"Price.3", type text}, {"Product.4", type text}, {"Qty.4", Int64.Type}, {"Price.4", type text}, {"Product.5", type text}, {"Qty.5", Int64.Type}, {"Price.5", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Campaign Name", "Name", "Contact No", "Date Created", "Index"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Attribute.2"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Product] <> " ")) in #"Filtered Rows"If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
