Expand table with all categories even if blank

[GBKYE2]

Hi all,

Not sure I'm phrasing the question well in the title but here's an example of what I'm trying to do. For eg let's say I have the below table:

Simulation	Colour	Count
1	Red	1
1	Orange	2
1	Yellow	2
1	Green	3
1	Blue	2
1	Indigo	1
1	Violet	2
2	Red	3
2	Yellow	2
2	Green	3
2	Blue	2
2	Violet	4

And I also have a key of all available colours:

Colour

Red

Orange

Yellow

Green

Blue

Indigo

Violet

I would like to transform the first table to show:

Simulation	Colour	Count
1	Red	1
1	Orange	2
1	Yellow	2
1	Green	3
1	Blue	2
1	Indigo	1
1	Violet	2
2	Red	3
2	Orange	0
2	Yellow	2
2	Green	3
2	Blue	2
2	Indigo	0
2	Violet	4

I tried merging the tables in a new query but that didn't add in the 0 rows.

Many thanks to anyone that can advise.

[ryan_mayu · Accepted solution]

@GBKYE2 

you can do this in PQ, pls see the attachment below

[GBKYE2]

Thanks, I'll give it a try!

[tamerj1]

Hi @GBKYE2 

Are the two tables linked through the color column? Are you trying to create a calculated table or a table visual?

[GBKYE2]

Hi tamerj1,

Yes the data are linked to the colour column. From the sounds of it I'm trying to create a calculated table but I originally thought I would have to do this through the queries.

[tamerj1]

Hi @GBKYE2 
Please refer to attached file with both solutions

[GBKYE2]

Thanks, I'll give both a try!