Restructuring Data in Power BI

[Igadeer]

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):

id	age_okay	education_okay	area_okay	motivation_okay	experience_okay	license_okay
11	yes	yes	yes	yes	yes	yes
22	yes	yes	maybe	yes	maybe	yes
33	yes	maybe	yes	yes	yes	yes
44	no	no	yes	yes	maybe	yes
55	maybe	no	no	no	no	no

The Target

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

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:

category	id	yes	maybe	no
age_okay	11	1
education_okay	11	1
area_okay	11	1
motivation_okay	11	1
experience_okay	11	1
license_okay	11	1
age_okay	22	1
education_okay	22	1
area_okay	22		1
motivation_okay	22	1
experience_okay	22		1
license_okay	22	1
age_okay	33		1
education_okay	33	1
area_okay	33	1
motivation_okay	33	1
experience_okay	33	1
license_okay	33	1
age_okay	44			1
education_okay	44			1
area_okay	44	1
motivation_okay	44	1
experience_okay	44		1
license_okay	44	1
age_okay	55			1
education_okay	55			1
area_okay	55			1
motivation_okay	55			1
experience_okay	55			1
license_okay	55			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!

[amitchandak · Accepted solution]

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

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