cancel
Showing results for
Did you mean:
Frequent Visitor

## 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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

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

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}}),
#"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 2
Community Support Team

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

Hi @sandviga ,

`= 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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

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

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}}),
#"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"

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors