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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors