Nested IF/AND Statement Power Query - Custom Column

[JennS]

I am stuck on converting a nested IF/AND statement from Excel to Power Query as a custom column. I have my data sorted in Power BI by the phone number, call date, and call time. I am stuck on how do the look up to the previous row and see if it meets the criteria. I am trying to tie the results to see the transfer routes of calls.

My excel formula is 

=IF(J11=0,0,IF(AND(I11=5,J10=0),B10,IF(J11=J10,B10,0)))

I am looking to achieve column L for my output in my new custom colum.

This is the formula I have in power query but it not looking at the previous row above and not calculating as a IF/AND but as an IF/OR.

= Table.AddColumn(#"Expanded ACD Transfer Mapping", "Custom", each if [orig_recid] = 0 then 0 else if [call_type] = 5 then [record_id] else if [orig_recid] = [orig_recid] then [record_id] else null)

[Vera_33 · Accepted solution]

Hi @JennS 

You need an Index column to refer the row above. One thing to consider, if there is a match in the first row, then no previous row, what should it return? you can wrap a try...otherwise

= Table.AddColumn(#"Added Index", "prior_recid", each if [orig_recid] = 0 then 0 
else if ([call_type] = 5 and #"Added Index"[orig_recid]{[Index]-1}=0 ) or [orig_recid] = #"Added Index"[orig_recid]{[Index]-1} then #"Added Index"[record_id]{[Index]-1}
else  null)

[JennS]

• Thank you so much Vera!  There most likely would not be a match in the first row due to how I am sorting the data but I did not think of this. I will test it more tomorrow with new data to see if this scenario does occur. Thank you so much for your help. The index column should solve this!