Add condition to VlookUp M code

[Mederic]

Hello everyone,

I would like to add a condition to the existing M code.
Can you please help me?
I would also like to know if Buffer() is necessary for this code, knowing that I actually have about 2,000 rows on one side and 40 rows for the lookup table

I'm sorry for the picture but I don't know how to insert an Excel file

Thanks in advance

Regards

[Mahesh0016]

@Mederic Please Share your dummy M code or Dataset.

[Mederic]

Helle @Mahesh0016 ,

Thanks for your message,

I have just seen that it is possible to insert tables
Here is the data, I hope it will be used
Thank you in advance

Regards

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Catégorie", type text}, {"Product_ID", type text}, {"Sale", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=> Table.SelectRows(LookUp, (y)=> Text.Contains(x[Product_ID], y[ID]))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Departement", "Warehouse"}, {"Departement", "Warehouse"})
in
    #"Expanded Custom"

ID	Departement	Warehouse
1	ABC	L1
2	DEF	L2
3	GHI	L3
4	JKL	L4

Catégorie	Product_ID	Sale
A	V1	15
A	V2	38
B	V1	34
A	V3	15
A	V5	32

[jennratten · Accepted solution]

Hello - you could add the condition at the step in which you lookup the value from the other table, however, because you are returning more than one column that then needs to be expanded, doing so would present a complication since one condition would return a table and the other would return text.  Instead, I recommend you add a conditional replacement after the expansion, like below.  Based on your dataset size, I don't think it would be necessary to buffer.

SCRIPT

Table.ReplaceValue(#"Expanded Custom",each [Warehouse], each if [Catégorie] = "B" then "P" else [Warehouse],Replacer.ReplaceText,{"Warehouse"})

RESULT

[Mederic]

Hello @jennratten ,
Thank you for your solution which works very well and the explanations 
Otherwise, it's fast overall with 2,000 lines (about 5-6 seconds to refresh)

Have a nice day

Regards

[jennratten]

You're welcome!