Forum Discussion
Pivot my data's rows into columns with custom headings
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.
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 " & _rankThis will have the following result when used as a Column in Matrix
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
5 Replies
- Mariusz
Community Champion
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 " & _rankThis will have the following result when used as a Column in Matrix
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.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?
- Mariusz
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 " & _rankI've added the Person to a visual as well, please see the attached.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
