Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
popobawa23
Frequent Visitor

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:

IDPersonAmountChecked byDate_checked
1John$200Andrew01/10/2019 12:34
1John$200James01/10/2019 15:30
1John$200Arthur01/10/2019 16:01
1John$200Sam02/10/2019 08:34
2Aisha$342James01/10/2019 16:34
2Aisha$342Sarah02/10/2019 10:13
2Aisha$342Sam03/10/2019 09:11

 

What I require is the following so I can analyze the time between checkers:

IDPersonAmountCheck 1Date 1Check 2Date 2Check 3Date 3Check 4Date 4
1John$200Andrew01/10/2019 12:34James01/10/2019 15:30Arthur01/10/2019 16:01Sam02/10/2019 08:34
2Aisha$342James01/10/2019 16:34Sarah02/10/2019 10:13Sam03/10/2019 09:11NULLNULL

 

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.

1 ACCEPTED SOLUTION
Mariusz
Community Champion
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 " & _rank

This will have the following result when used as a Column in Matrix

image.png

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

5 REPLIES 5
Mariusz
Community Champion
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 " & _rank

This will have the following result when used as a Column in Matrix

image.png

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski

 

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.

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski

 

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.
Mariusz Repczynski


 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.