Forum Discussion
Combining rows by order
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.
Could you please highlight the difference between the two images. Which column do you need to combine.
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
Create a Calculated Coulmn aas given below.
Column= TableName[line2_ID]&" "&TableName[line2_Des]&" "&TableName[line2_price]&" "&TableName[line3_ID]&" "&TableName[line2_Des]&" "&TableName[line2_price]
hthota wrote:Create a Calculated Coulmn aas given below.
Column= TableName[line2_ID]&" "&TableName[line2_Des]&" "&TableName[line2_price]&" "&TableName[line3_ID]&" "&TableName[line2_Des]&" "&TableName[line2_price]
I am trying to get a combined row per order with each order having an unknown number of lines. The [line#_id] don't exist yet so the above calculation gives me an error. Am I missing something?
The only similar posts I found on this are below.
https://community.powerbi.com/t5/Desktop/Combine-values-of-multiple-rows-in-one-row/td-p/308919
