Forum Discussion
Solved
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...
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.
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.
Hi v-kkf-msft , by importing this excel file and applied the same steps, it doesn't work. The Qty and Price skip one row down as shown in screenshot below. How to fix this?
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.