Power Query to Create Custom "Visit_Number" Column

[puru85]

Hello Experts,

Below is my data:

Date	Customer_ID	Invoice
1/1/2022	A1	IN-01
1/13/2022	A2	IN-02
1/13/2022	A2	IN-03
2/5/2022	A1	IN-04
7/8/2022	A2	IN-05
8/8/2022	A3	IN-06
8/9/2022	A3	IN-07
8/15/2022	A4	IN-08
8/15/2022	A4	IN-09
9/2/2022	A2	IN-10
9/2/2022	A2	IN-11
9/10/2022	A4	IN-12

My Expected result is:

Customer_ID	Visit_Date	Invoice	Visit_Number
A1	1/1/2022	IN-01	1
A1	2/5/2022	IN-04	2
A2	1/13/2022	IN-02	1
A2	1/13/2022	IN-03	1
A2	7/8/2022	IN-05	2
A2	9/2/2022	IN-10	3
A2	9/2/2022	IN-11	3
A3	8/8/2022	IN-06	1
A3	8/9/2022	IN-07	2
A4	8/15/2022	IN-08	1
A4	8/15/2022	IN-09	1
A4	9/10/2022	IN-12	2

Requirement details are:
1) To achive in Powerquery ( Backend)
2)  To create a custom column "Visit_Number"  
3) invoices on the same date for the same Customer_iD are considered a single visit, and each new date with an invoice increments the visit count.

4) To understand more clear on the requirement, Please look at the cells marked in red on My Expected result.

Please help me. 

Thank you 

[puru85]

Awesome @TomMartens 🙂

[TomMartens · Accepted solution]

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-ZZu3YrUMBoBjOk...

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