Pivot my data's rows into columns with custom headings

[popobawa23]

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.

[Mariusz · Accepted 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

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[popobawa23]

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 " & _rank

I'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.

[popobawa23]

Thank you so much @Mariusz. I've never received such quick and useful help on any forum ever 😃.

[Mariusz]

Hi @popobawa23 

No worries, glad I could help!

Many Thanks,
Mariusz

Please feel free to connect with me.