Forum Discussion

metcala's avatar
metcala
Helper III
3 years ago
Solved

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 Ban...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    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]))

     

     

jgeddes's avatar
jgeddes
Super User
3 years ago

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]))