Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
and a little trick.
The trick is explained by the two queries
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
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
and a little trick.
The trick is explained by the two queries
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
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |