Getting value based off Rank

[dmace]

I have a dataset of candidates that have one row per ReferredByWorker. So a candidate whom 3 people referred has three rows. I needed to consolidate the information into one row which I accomplished by creating a Rank in Power Query and then using the following DAX column to get the ReferredByWorkers 

ReferredByWorker1 = 
VAR cand = 'ReferralSplitsBase'[Candidate_ID_Workday]
RETURN
MAXX(
    FILTER(
        ALL('ReferralSplitsBase'),
        'ReferralSplitsBase'[Rank] = "1"
        &&
        'ReferralSplitsBase'[Candidate_ID_Workday] = cand
    ),
    IF('ReferralSplitsBase'[ReferredByWorker] <> BLANK(), 'ReferralSplitsBase'[ReferredByWorker], [ReferredByForWD]))

We wanted to go out as far as four referrals so there are four columns using this logic, but incrementing the Rank each time. This is extremely slow but it worked so I dealt with it, now that the project is settling down I want to refactor this using Power Query. I attempted to use the following logic but received a cyclic reference error. 

ReferredByWorker1 = 
let
   cand = [Candidate_ID_Workday],
   filterTable = Table.SelectRows(
                   Table.SelectColumns(ReferralSplitsBase, {"Rank", "Candidate_ID_Workday", "ReferredByWorker", "ReferredByForWD"}),
                   each ([Rank] = "1" and [Candidate_ID_Workday] = cand)
                 ),
   maxFiltered = List.Max(Table.Column(filterTable, "ReferredByWorker"))
in
   if maxFiltered <> null then maxFiltered else [ReferredByForWD]

The reason for the ReferredByForWD column is because this data set is pulling data from two sources which are drastically different formats, so I had to do a good bit of transformation to marry them up. Can anyone provide any insight on how to get this to work?

[v-xinruzhu-msft · Accepted solution]

Hi @dmace 

1.You can consider to create a coustom column named 'Custom' directly and input the following code

=List.Max(Table.SelectRows(the last step name(e.g #"Changed Type"),(x)=>x[Rank]=1 and x[Candidate_ID_Workday]=[Candidate_ID_Workday])[ReferredByWorker])

 2.Then add a new custom column named 'Custom1'

=if [Custom] <> null then [Custom] else [ReferredByForWD]

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.