Structuring dataset for better visualization

[ReneeKoelewyn]

Good day,

I am struggling to structure my dataset so that the x-axis represents the campuses and the qualifications are grouped per year per campus. I have included an example of what the clustered column chart looks like and an example of the dataset. So basically, I want to swap them, but when I do that, it does not group the campus together for both years. I tried transposing the data and adding a column for the year and campus, but I got an error that items are duplicated. 

On the right-hand side, it lists the four campuses' information for 2023 and 2024, but I would like the chart to show the bars for each year for each program for each campus. 

I hope my question makes sense 🙂

[Anonymous · Accepted solution]

Thanks for the concern from Ritaf1983.

Hi @ReneeKoelewyn ,

Based on your description, I created simple data:

I performed the following:

1.Select the first column and unpivot the other rows in Power Query Editor.

2.Change data type:

3.Create two new columns:

Year = 20&LEFT([Attribute],2)

Campus = RIGHT([Attribute], LEN([Attribute]) - 2)

Result:

If you still have any questions, can you provide a more detailed description or a .pbix file with the private data removed.

Best Regards,
Zhu

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

[Ritaf1983]

Hi @ReneeKoelewyn 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly