Forum Discussion
Custom Formula Look Up Value For Matching Criteria
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
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]))
1 Reply
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]))