Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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)
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |