Custom Formula Look Up Value For Matching Criteria

[metcala]

Hi

I am struggling a little bit to find a solution to correct some data. Here is the data structure:

Period	Employee ID	Pay Band	Category	Cost
1/1	123	NULL	A	50
1/1	123	Band 2	B	100
1/1	123	NULL	C	12
2/1	123	NULL	A	60
2/1	123	Band 3	B	40
2/1	123	NULL	C	30

My current thinking is to add a Revised Pay Band column that looks up Period and Employee ID and if it finds a Pay Band value that is not null it populates the Revised Pay Band.

Here is the desired outcome but open to suggestions if there is a more optimal solution:

Period	Employee ID	Pay Band	Category	Cost	Revised Pay Band
1/1	123	NULL	A	50	Band 2
1/1	123	Band 2	B	100	Band 2
1/1	123	NULL	C	12	Band 2
2/1	123	NULL	A	60	Band 3
2/1	123	Band 3	B	40	Band 3
2/1	123	NULL	C	30	Band 3

I have tried it in DAX and couldn't get it working but think this is probably something that should be done in Power Query anyway.

Any help or pointers would be much appreciated!

Thanks

[jgeddes · Accepted solution]

The following code adds a column that returns the first [Pay Band] value that is not null for the value of [Period] and [Employee ID] for the row being evaluated.

Table.AddColumn(#"Previous Step", "revisedPayBand", (x) => List.First(Table.SelectRows(#"Previous Step", each [Period] = x[Period] and [Employee ID] = x[Employee ID] and [Pay Band] <> null)[Pay Band]))