How can I aggregate and display separate columns containing similar data into a single chart?

[earthling]

I have data that displays 'Learning Outcomes'  in individual columns:

There are a total of 9 outcomes to which I need to be able to display their total counts in a stacked bar chart (with learning outcome on x-axis, count on y), however, since they are all seperate columns I am having difficulty because you cannot have multiple individual x-axis.

Does there exist a solution/alternative/specific function to aggregate the 9 columns into 'one' - or at least be able to show the distribution among them in a single chart?

[Anonymous · Accepted solution]

Hi @earthling ,

In Power Query Editor, select 9 columns and use Unpivot feature.

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882... 

Or in Power BI Desktop, create a new table by using below dax formula:

Table 2 = union(
SELECTCOLUMNS('Table',"append",'Table'[Column1]),
SELECTCOLUMNS('Table',"append",'Table'[Column2]),
SELECTCOLUMNS('Table',"append",'Table'[Column3]),
...)

Best Regards,

Jay

[Fowmy]

@earthling 

You can apply the UnPivot function in Powr Query to achieve the excpected results.
Please refer to these video to learn how: https://youtube.com/playlist?list=PL17mrTZhD0ZpyjSCZdLQx-0h3hmTRD9hR

If you create and pate a dummy dataset in your reply with the expected result, I will be able to show you how.