Try Otherwise in M doesn't work when multiple rows meet the condition

[gavin007]

I am trying to replace the internal sales account number with the external sales accounts number when the ExtInt="I" (indicating an internal sales), I use try Dax to search each row in source query where it meets the following conditions,

1. internal sales  OrderNO_Revised is the same as external sales

2. ExtInt="C" 

When the condition is met, the replacement happen. 

e.g. OrderNo_Revised=60S027143 with Customer Code_Revised IT000007 has been replaced with external customer code CL0002633.

My problem is when there are two identical OrderNo_Revised in external sale lines (yellow retangle) the Try formula doesn't work (red retangle shows the internal customer code is still IC0002851 as opposed to CL0002844). Can anyone help or suggest alternative to 

I upload the folder in onedrive, you need to reconnect the source file when you refresh the query. the try script is in Recco query.

https://1drv.ms/u/s!Aig3EWdV94jKgfAJ74rRtH77XZfbkA?e=Jt1dIa

[Fowmy]

@gavin007 

You have used try---otherwise, it hides the error. When you have multiple results, your code breaks. Remove your code and try to add a new column using the following code, if multiple results are returned, I get the MAX of it.

= Table.AddColumn(Source, "Custom", each 
if [ExtInt]="I" then  
 let Order = [OrderNo_Revised] ,
   Cust =  List.Max(Table.SelectRows(Source, each [ExtInt] = "C" and [OrderNo_Revised] = Order )[Customer Code_Revised])
 in if Cust=null then [Customer Code_Revised] else Cust
else
[Customer Code_Revised]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[gavin007]

Hi Fowmy, it is working in the samples. However, when i put in actual daily sales files with more than 80,000 rows,  it loops through the source every time it evaluate the formula. it takes more than 8 hours and still counting to apply the query.

Is there a way to make it more efficiency. like don't evaluate the external customer, only the internal.