Forum Discussion
How to Use Power Query to Group Sequential Numbers and Non-Sequential Numbers into one String
- 7 years ago
Figured out the solution after reviewing several Google search results and combining solutions from multiple sources. I probably have a few extra uneccessary steps in there, but I get the result I needed so I'm not going to mess with it.
let
Source = Excel.CurrentWorkbook(){[Name="dateTable"]}[Content],
#"Sorted Rows" = Table.Buffer(Table.Sort(Source,{{"Invoice", Order.Ascending}})),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Client", "Invoice"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Sorted Rows1" = Table.Sort(#"Removed Duplicates",{{"Invoice", Order.Ascending}, {"Client", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1),
#"Inserted Subtraction" = Table.AddColumn(#"Added Index", "Subtraction", each [Invoice] - [Index], type number),
#"Sorted Rows2" = Table.Sort(#"Inserted Subtraction",{{"Client", Order.Ascending}, {"Invoice", Order.Ascending}}),
#"Grouped Rows1" = Table.Group(#"Sorted Rows2", {"Subtraction", "Client"}, {{"Min", each List.Min([Invoice]), type number}, {"Max", each List.Max([Invoice]), type number}}),
#"Inserted Merged Column" = Table.AddColumn(#"Grouped Rows1", "Merged Invoices", each Text.Combine({Text.From([Min], "en-US"), Text.From([Max], "en-US")}, "-"), type text),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column", "Invoice Range", each if [Min] = [Max] then [Min] else [Merged Invoices]),
#"Sorted Rows3" = Table.Sort(#"Added Conditional Column",{{"Min", Order.Ascending}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Sorted Rows3",{"Client", "Invoice Range"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Invoice Range", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Client"}, {{"All Invoices", each Text.Combine(_[Invoice Range],", "), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Client", "Client ID"}, {"All Invoices", "Invoices"}}),in
#"Renamed Columns"
Figured out the solution after reviewing several Google search results and combining solutions from multiple sources. I probably have a few extra uneccessary steps in there, but I get the result I needed so I'm not going to mess with it.
let
Source = Excel.CurrentWorkbook(){[Name="dateTable"]}[Content],
#"Sorted Rows" = Table.Buffer(Table.Sort(Source,{{"Invoice", Order.Ascending}})),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Client", "Invoice"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Sorted Rows1" = Table.Sort(#"Removed Duplicates",{{"Invoice", Order.Ascending}, {"Client", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1),
#"Inserted Subtraction" = Table.AddColumn(#"Added Index", "Subtraction", each [Invoice] - [Index], type number),
#"Sorted Rows2" = Table.Sort(#"Inserted Subtraction",{{"Client", Order.Ascending}, {"Invoice", Order.Ascending}}),
#"Grouped Rows1" = Table.Group(#"Sorted Rows2", {"Subtraction", "Client"}, {{"Min", each List.Min([Invoice]), type number}, {"Max", each List.Max([Invoice]), type number}}),
#"Inserted Merged Column" = Table.AddColumn(#"Grouped Rows1", "Merged Invoices", each Text.Combine({Text.From([Min], "en-US"), Text.From([Max], "en-US")}, "-"), type text),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column", "Invoice Range", each if [Min] = [Max] then [Min] else [Merged Invoices]),
#"Sorted Rows3" = Table.Sort(#"Added Conditional Column",{{"Min", Order.Ascending}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Sorted Rows3",{"Client", "Invoice Range"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Invoice Range", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Client"}, {{"All Invoices", each Text.Combine(_[Invoice Range],", "), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Client", "Client ID"}, {"All Invoices", "Invoices"}}),
in
#"Renamed Columns"