Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi everyone,
I have a data set that has repeating IDs, and I want it to have each ID as its own entry, and the repeating rows as column entries. Below is the original data:
ID | Person | Amount | Checked by | Date_checked |
1 | John | $200 | Andrew | 01/10/2019 12:34 |
1 | John | $200 | James | 01/10/2019 15:30 |
1 | John | $200 | Arthur | 01/10/2019 16:01 |
1 | John | $200 | Sam | 02/10/2019 08:34 |
2 | Aisha | $342 | James | 01/10/2019 16:34 |
2 | Aisha | $342 | Sarah | 02/10/2019 10:13 |
2 | Aisha | $342 | Sam | 03/10/2019 09:11 |
What I require is the following so I can analyze the time between checkers:
ID | Person | Amount | Check 1 | Date 1 | Check 2 | Date 2 | Check 3 | Date 3 | Check 4 | Date 4 |
1 | John | $200 | Andrew | 01/10/2019 12:34 | James | 01/10/2019 15:30 | Arthur | 01/10/2019 16:01 | Sam | 02/10/2019 08:34 |
2 | Aisha | $342 | James | 01/10/2019 16:34 | Sarah | 02/10/2019 10:13 | Sam | 03/10/2019 09:11 | NULL | NULL |
I tried using the matrix visualization but it only lets me see first and last checkers (and even then, the name shown is incorrect). Please help me out, I've been racking my brain for days trying to figure out how to handle this data.
Solved! Go to Solution.
Hi @popobawa23
You can add a column to your table that will rank your checks like below.
Check Rank = VAR _id = 'Table'[ID] VAR _rank = RANKX( FILTER( 'Table', 'Table'[ID] = _id ), 'Table'[Date_checked] ) RETURN "Check " & _rank
This will have the following result when used as a Column in Matrix
Hi @popobawa23
You can add a column to your table that will rank your checks like below.
Check Rank = VAR _id = 'Table'[ID] VAR _rank = RANKX( FILTER( 'Table', 'Table'[ID] = _id ), 'Table'[Date_checked] ) RETURN "Check " & _rank
This will have the following result when used as a Column in Matrix
This is great @Mariusz, works just like you showed 😄. I have 2 issues though, Is it possible to add the name of the person? and also to reverse the order of the checkers to start from the first one to the last checker?
Hi @popobawa23
Sure, to change the order, all you need to do is add an extra argument to RANKX function, like below.
Check Rank = VAR _id = 'Table'[ID] VAR _rank = RANKX( FILTER( 'Table', 'Table'[ID] = _id ), 'Table'[Date_checked],, ASC ) RETURN "Check " & _rank
I've added the Person to a visual as well, please see the attached.
Thank you so much @Mariusz. I've never received such quick and useful help on any forum ever 😃.
Hi @popobawa23
No worries, glad I could help!
Many Thanks,
Mariusz
Please feel free to connect with me.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
81 | |
76 | |
61 | |
37 | |
33 |
User | Count |
---|---|
99 | |
56 | |
51 | |
42 | |
40 |