How do I create a single bar chart from three independent data sources?

Question

Input

I have three datasets (simplified example below):

• List of students from school A and their age (1200 records)
• List of students from school B and their age (200 records)
• List of students from school C and their age (2000 records)

Every dataset has two columns: studentId and age.

Desired result

I want to create a bar chart that shows the distribution of age across these schools. For easy comparison I would like to have:

• one bar chart
• with three legend entries:
  • A
  • B
  • C
• 7 categories:
  • age =< 12
  • age = 13
  • ...
  • age >= 18

What I have tried

I tried linking the datasets, but due to the different number of students and the various studentId formats per school this did not work.

I tried using the studentId columns of the three datasets as the value and the age column of every dataset as the axis. This only worked one dataset at a time. I needed to "Go to the next level in the hierarchy" to see the other dataset.

How else can I achieve this?

Answer 1

You can go into Power Query Editor:

And you will need to add a new column for each datasheet with the legend you want, for example:

So your tables should look like this:

Then merge the tables with Append Queries as New:

Select Three or more tables, and add the three tables:

And there you have your three tables merged into one.