Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello!
Another day, another Power BI issue...
I have a dataset consisting of a unique list of customers, something like this:
Contract ID | Customer Name | Sales Person | Post Code Area |
1234 | Customer A | Tim Jones | BS |
1235 | Customer B | Tim Jones | TA |
1236 | Customer C | Tim Jones | EX |
1237 | Customer D | Sarah Roberts | WA |
1238 | Customer A | Tim Jones | BS |
1239 | Customer A | Tim Jones | BS |
1240 | Customer D | Sarah Roberts | WA |
1241 | Customer E | Sarah Roberts | SY |
1242 | Customer B | Tim Jones | TA |
1243 | Customer E | Sarah Roberts | SY |
1244 | Customer D | Sarah Roberts | WA |
1245 | Customer F | Sarah Roberts | CW |
I also have a list of product sales by some (but not all of the above customers). In some instances a customer may have made multiple purchases. So, the sales history table would look something like this:
PURCHASE HISTORY | ||
Contract ID | Product | Purchase Date |
1237 | Product A | 03/01/2023 |
1244 | Product C | 03/01/2023 |
1244 | Product A | 09/01/2023 |
1235 | Product B | 15/01/2023 |
1235 | Product A | 23/02/2023 |
1235 | Product B | 24/02/2023 |
1234 | Product A | 08/03/2023 |
1242 | Product B | 01/04/2023 |
1242 | Product C | 01/04/2023 |
1245 | Product C | 22/03/2023 |
1245 | Product A | 25/03/2023 |
1245 | Product A | 25/03/2023 |
1245 | Product A | 03/04/2023 |
1240 | Product B | 30/01/2023 |
I need to either add a column to the customers list stating the last purchase date, e.g...
Contract ID | Customer Name | Sales Person | Post Code Area | Last Purchase |
1234 | Customer A | Tim Jones | BS | 08/03/2023 |
1235 | Customer B | Tim Jones | TA | 24/02/2023 |
1236 | Customer C | Tim Jones | EX | NULL |
1237 | Customer D | Sarah Roberts | WA | 03/01/2023 |
1238 | Customer A | Tim Jones | BS | NULL |
1239 | Customer A | Tim Jones | BS | NULL |
1240 | Customer D | Sarah Roberts | WA | 30/01/2023 |
1241 | Customer E | Sarah Roberts | SY | NULL |
1242 | Customer B | Tim Jones | TA | 01/04/2023 |
1243 | Customer E | Sarah Roberts | SY | NULL |
1244 | Customer D | Sarah Roberts | WA | 09/01/2023 |
1245 | Customer F | Sarah Roberts | CW | 03/04/2023 |
Or to create a new measure that will do the same thing.
Ultimately, I'm looking to map a sales persons customers and colour code them according to whether they've made a purchase or not.
Any help, as always would be greatly appreciated.
Thanks
RDF
Solved! Go to Solution.
Dear ,
In power Query do the same as follow :
1 - Group list 2 by max date
2 - Merge Queries in list 1 with list 2
3- Expand List 2 , it will show up the max date that match with the contract ID
Attached Images and Attached PBIX file link
https://www.dropbox.com/s/e13zbo13scm5myt/Max%20Date.pbix?dl=0
Regards ,
Kindly accept as a solution and a kudo is appreciated .
@bhelou - thank you for your solution, and especially for the step-by-step images - they were especially useful for a beginner like myself.
I got a little confused about how to Expand the column. After the merge I had a column in my data displaying "table". After a little more googling, I realised I simply had to click on the double-arrow icon in the column header and select the column I wanted to bring in/expand. Hope that helps any other newbie like myself that need everything spelt out for them!
Regards
RDF
Dear ,
In power Query do the same as follow :
1 - Group list 2 by max date
2 - Merge Queries in list 1 with list 2
3- Expand List 2 , it will show up the max date that match with the contract ID
Attached Images and Attached PBIX file link
https://www.dropbox.com/s/e13zbo13scm5myt/Max%20Date.pbix?dl=0
Regards ,
Kindly accept as a solution and a kudo is appreciated .
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |