cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JennS
Frequent Visitor

Nested IF/AND Statement Power Query - Custom Column

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

JennS_0-1629245455949.png

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)

 

 

 

1 ACCEPTED SOLUTION
Vera_33
Super User
Super User

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)

 

View solution in original post

2 REPLIES 2
Vera_33
Super User
Super User

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)

 

View solution in original post

JennS
Frequent Visitor

  • 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! 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors