Forum Discussion
Solved
Combining rows by order
I have data from our sales system that I am trying to prepare for an API connection. The orders are split into multiple rows for item detail and I am looking for a way to get the orders & items combi...
Hi,
Given the Table shared by MarkLaf, this M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"transaction", type text}, {"customer", type text}, {"line_ID", Int64.Type}, {"line_description", type text}, {"line__price", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"transaction", "customer"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"transaction", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"customer", "Value", "Index"}, {"customer", "Value", "Index"}), #"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",{{"Merged.1", Order.Ascending}, {"Index", Order.Ascending}, {"Merged.2", Order.Ascending}}), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value") in #"Pivoted Column"Hope this helps.
Its a combination of the second order. See red box for the new columns.
hthota wrote:Could you please highlight the difference between the two images. Which column do you need to combine.
Hi,
Given the Table shared by MarkLaf, this M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"transaction", type text}, {"customer", type text}, {"line_ID", Int64.Type}, {"line_description", type text}, {"line__price", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"transaction", "customer"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"transaction", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"customer", "Value", "Index"}, {"customer", "Value", "Index"}),
#"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",{{"Merged.1", Order.Ascending}, {"Index", Order.Ascending}, {"Merged.2", Order.Ascending}}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value")
in
#"Pivoted Column"
Hope this helps.
Thanks everyone! This is what i needed to get this project finished.
-Tom
You are welcome.