Fill in Missing Weeks

[Scraps822]

Hi Everyone,

Thank you in advance for looking at this.

I can't figure out this request:

I have two tables that looks like this:

Genre	Week	% total
Horror	4	.35
Horror	3	.42
Horror	2	.12
Horror	1	.25

Genre	Movie	Week	% Movie
Horror	Grudge	4	.25
Horror	Grudge	2	.23
Horror	Grudge	1	.1
Horror	Evil Dead	4	.6
Horror	Evil Dead	2	.8

I would like to combine to make the genre totals in one column. At the same time make it that the Week will all be consistent. So even though there are missing Week because there is no data in the %Movie there is still a % Total

Genre	Movie	Week	% Movie	% Total
Horror	Grudge	4	.25	.35
Horror	Grudge	3		.42
Horror	Grudge	2	.23	.12
Horror	Grudge	1	.1	.25
Horror	Evil Dead	4	.6	.35
Horror	Evil Dead	3		.42
Horror	Evil Dead	2	.8	.12
Horror	Evil Dead	1		.25

Any ideas?

Thank you again!

[jdunkerley79 · Accepted solution]

A little more complex than I would like but try this.

Make a unique genre, movie list (using a Unique Tool)

Join this to the Genre table (just connecting Genre)

This will create the Genre, Movie, Week list we need

Then join back to movies table to get the % Movie.

Youll need a union to not drop any records.

Flow looks like:

Sample attached.

[Scraps822]

You sir are a genius!

It works like a charm. Been on it for a two days now.

Thank you.