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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
biswad
Helper I
Helper I

How to view row data as column without loosing duplicate raw values - Power bi

Hi Friends, 

 

I'm working on a dashboard where I have the data in this format t1. 

t1

BranchOfficialNameRole
BarnetDavidTreasure
BedfordMikeSecretary
AngleseyAndyPresident
BedfordJohnSecretary

 

I would like to view the data so that the roles becomes a column and show officials under each roles like this . I tried to use Pivot in my store precedure before brining this data into power bi but my only problem using Pivot is that I had to use Max(Officialname) and due to which official who has duplicate roles under same branch were excluded and showed me the first row. For example In the branch "Bedford" there are two "Secretary" roles under officialname "John" and "Mike", using pivot I was getting only "Mike", I was wondering if there are any option in Powerbi to represent the data as shown in the second table t2 please. Any help would be greatly appriciated.

t2

BranchTreasurerSecretaryPresident
BarnetDavid  
Bedford Mike 
Anglesey  Andy
Bedford John 
2 ACCEPTED SOLUTIONS

Try this ...

 

Click here to download PBIX from OneDrive 

 

Use a Matrix visual with Branch and Official Name as Rows, the Role as Column and First OffcialName as Value

 

Please click Accept As Solution and Thumbs up. Thank you

 

speedramps_0-1706708648031.png

 

View solution in original post

v-yifanw-msft
Community Support
Community Support

Hi @biswad ,

Based on the information you provided, since the Rows of the matrix duplicate data will only be displayed once. You can add an index column. Use index and Branch as Rows.
Final output:

vyifanwmsft_0-1706766773579.png

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-yifanw-msft
Community Support
Community Support

Hi @biswad ,

Based on the information you provided, since the Rows of the matrix duplicate data will only be displayed once. You can add an index column. Use index and Branch as Rows.
Final output:

vyifanwmsft_0-1706766773579.png

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

speedramps
Super User
Super User

Try this solution ...

Click to download PBIX from OneDrive 

 

How it works ...

Use a Matrix visual ...

speedramps_0-1706699077505.png

 

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

Hi @speedramps thank you for this , however in this case I loose the duplicate roles under the same branch, for example  "Bedford" there are two "Secretary" roles under officialname "John" and "Mike", using this option I am getting only "Mike" not "John", the result is similar to Pivot. This was my issue, I wanted to bring both "John" and "Mike", again thank you for trying to help me out, much appriciated. 

Try this ...

 

Click here to download PBIX from OneDrive 

 

Use a Matrix visual with Branch and Official Name as Rows, the Role as Column and First OffcialName as Value

 

Please click Accept As Solution and Thumbs up. Thank you

 

speedramps_0-1706708648031.png

 

Thank you @speedramps and @v-yifanw-msft  both solves the answer to the problem, much appriciated all your support, I'm not sure if I could accept both as solution or not, but truely appriciated your help on this. 

Uspace87
Resolver III
Resolver III

Hi @biswad 

 

Have you tried Pivoting the Role Column? you will have a different structure but you can keep the duplicates:

 

Uspace87_0-1706698333137.png

 

 

Sorry I meant to Pivot Branch and select Role as Values Column awithout aggregation.

Thank you, I have tried pivoting the branch, but I need to keep the same structure as shown in the second table, if I pivot the branch the branch name goes under "Treasurer","Secretary" and "President". I would like to keep official roles under each role.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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