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
Igadeer
Frequent Visitor

Restructuring Data in Power BI

Hi there!

 

I'm currently trying to display some data in my report using a 100% Stacked Bar Chart, but the data isn't structured in a way that this visual supports. I'm looking for a way to restructure the data but haven't found a good way to do that just yet. I'd love it if some of you could help me figure this one out! You can download a sample .pbix file for this problem here: https://www.dropbox.com/s/qvxxh0mdcvtaw1i/Sample.pbix?dl=0 😉

 

The situation

The data I'm working with represents applications, and the various categories using which the applications are judged. There's a column for each category, and a row for each application (identified using a numerical ID). For each category, there's an option for yes, maybe and no. The data is structured as such (data and categories are fake):

idage_okayeducation_okayarea_okaymotivation_okayexperience_okaylicense_okay
11yesyesyesyesyesyes
22yesyesmaybeyesmaybeyes
33yesmaybeyesyesyesyes
44nonoyesyesmaybeyes
55maybenonononono

 

The Target

My target is to display this data in a 100% Stacked Bar Chart, which will look something like this:

Igadeer_0-1656939554498.png

This way, it's easy to see all of the data in a single visual, and it's easy to compare what categories applicants are most commonly rejected for. The data can be filtered further by ID and a bunch of other data in the full report. Problem is... with the way the data is currently structured, there doesn't seem to be a way to display the data like this.

 

My idea was to create a second table in which the data is structured differently (keeping the original intact for other visuals). This second table would have a layout like this:

categoryidyesmaybeno
age_okay111  
education_okay111  
area_okay111  
motivation_okay111  
experience_okay111  
license_okay111  
age_okay221  
education_okay221  
area_okay22 1 
motivation_okay221  
experience_okay22 1 
license_okay221  
age_okay33 1 
education_okay331  
area_okay331  
motivation_okay331  
experience_okay331  
license_okay331  
age_okay44  1
education_okay44  1
area_okay441  
motivation_okay441  
experience_okay44 1 
license_okay441  
age_okay55  1
education_okay55  1
area_okay55  1
motivation_okay55  1
experience_okay55  1
license_okay55  1

 

With this data, I can easily place each category on the Y-axis, and place the yes, maybe and no values on the X-axis. I've included the above table in the .pbix file, and used that to create the visual seen above.

 

The steps to transform this data would be something along these lines, in my mind:

  1. Create columns for category, ID and yes/maybe/no
  2. For each ID, create six rows (one for each category)
  3. Check the source table to fill in the various rows that have been created

 

The Question

So my question to you all is: how would I restructure the data like this in Power BI? I'm quite new to DAX and PowerQuery, so while I've tried a couple of things I haven't been able to create the table I need. If there's a better way to display the data in a single visual, I'd be open to suggestions for that too!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

1 REPLY 1
amitchandak
Super User
Super User

@Igadeer , Unpivot the first table. If required pivot again

 

https://radacad.com/pivot-and-unpivot-with-power-bi

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

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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