Forum Discussion
How to Use Power Query to Group Sequential Numbers and Non-Sequential Numbers into one String
In Excel, I have two columns, Client ID, and Invoice Number. Like this sample:
| Client Number | Invoice Number |
| 10 | 3205 |
| 12 | 3251 |
| 10 | 3200 |
| 12 | 3250 |
| 12 | 3255 |
| 10 | 3206 |
| 10 | 3207 |
Desired output would be as follows. How can I get Power Query to do this?
| Client Number | Invoice Number |
| 10 | 3200, 3205-3207 |
| 12 | 3250-3251, 3255 |
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"
2 Replies
Hi sandviga ,
Please try the formula below.
= Table.Group(#"Changed Type", {"Client Number"}, {{"Column", each Text.Combine(List.Transform(_[Invoice Number], (x) => Number.ToText(x)), ","), type text}})Here is my output.
In addition, you could refer to the M query below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyNjIwVYrVAfKMwDxTQwgPKmeAIofKM0VRaYbCM1eKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Client Number" = _t, #"Invoice Number" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client Number", Int64.Type}, {"Invoice Number", Int64.Type}}), #"a"= Table.Group(#"Changed Type", {"Client Number"}, {{"Column", each Text.Combine(List.Transform(_[Invoice Number], (x) => Number.ToText(x)), ","), type text}}) in #"a"Best Regards,
Cherry
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"
