Forum Discussion

sandviga's avatar
sandviga
New Member
7 years ago
Solved

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...
  • sandviga's avatar
    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"