Power Query to Create Custom "Visit_Number" Column

Hey puru85 ,

 

here, you will find a pbix that contains a solution that creates the below table: https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EYnAvS_rOA1Ctd-ZZu3YrUMBoBjOk4vGpecvPIw9-GYQzA?e=zfMfV3

 

The table "RowIndexInGroupRankDense" looks like this:

The magic is done by the function Table.AddRankColumn

• the function in general: Table.AddRankColumn - PowerQuery M | Microsoft Learn
• the RankKind type: RankKind.Type - PowerQuery M | Microsoft Learn

and a little trick.

The trick is explained by the two queries

1. 1-RowIndexInGroup-Grouping
2. 2-RowIndexInGroup-Formatted

This might be helpful because after "adjusting" the grouping function the grouping dialog can not be rendered any longer:

The trick, start with grouping the rows by customer and tweaking the grouping. The next screenshot shows the grouping:

 

The code snippet below shows the M-code after the grouping but formatted:

 

 

#"Grouped Rows" = 
        Table.Group(#"Changed Type", {"Customer_ID"}, 
            {
                {"AllRows", 
                    each _ 
                    , type table [Date=nullable date, Customer_ID=nullable text, Invoice=nullable text]
                }
            }
        )

 

 

 

Then the line

each _

has to be adapted with the aggregation function, e.g.:  Table.AddRankColumn

and the row with table definition must be removed.

Finally, expand the Table:

Hopefully, this provides what you are looking for.

 

Regards,

Tom