functions to smartly assign sequential numbers for each group

[hiroki]

Hello guys.

I have wrote functions to assign sequential numbers for each group that everyone wanted but hadn't yet invented.

https://github.com/takeyamajp/PowerQuery-IndexRowsForEachGroup

In this library, it is possible to assign sequential numbers in the exact and intended order.
Also, This library fully preserves column type information.

Custom Functions

• IndexRowsForEachGroup(table as table, key as anynonnull, comparisonCriteria as anynonnull, optional newColumnName as nullable text) as table
• CountRowsForEachGroup(table as table, key as anynonnull, optional newColumnName as nullable text) as table
• IndexGroups(table as table, key as anynonnull, comparisonCriteria as anynonnull, optional newColumnName as nullable text) as table
• CountGroups(table as table, key as anynonnull, optional newColumnName as nullable text) as table
• IndexRows(table as table, comparisonCriteria as anynonnull, optional newColumnName as nullable text) as table
• CountRows(table as table, optional newColumnName as nullable text) as table

 

Code Sample

let
    Source = #table(type table[Region = text, Country = text], {
        {"Asia", "Australia"}, 
        {"Asia", "China"}, 
        {"Asia", "India"}, 
        {"Asia", "Japan"}, 
        {"Europe", "France"}, 
        {"Europe", "Germany"}, 
        {"Europe", "United Kingdom"}, 
        {"North America", "Canada"}, 
        {"North America", "United States"}
    }),
    Custom1 = IndexRowsForEachGroup(Source, "Region", {"Region", "Country"}, "INDEX1"),
    Custom2 = CountRowsForEachGroup(Custom1, "Region", "COUNT1"),
    Custom3 = IndexGroups(Custom2, "Region", {"Region", "Country"}, "INDEX2"),
    Custom4 = CountGroups(Custom3, "Region", "COUNT2"),
    Custom5 = IndexRows(Custom4, {"Region", "Country"}, "INDEX3"),
    Custom6 = CountRows(Custom5, "COUNT3")
in
    Custom6

[Peter_Beck]

Thanks for these, I think they will be useful.

Cheers,

Peter