How to Use Power Query to Group Sequential Numbers and Non-Sequential Numbers into one String

[sandviga]

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

[sandviga · Accepted solution]

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"

[v-piga-msft]

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