How to apply Index/Match/If for array function in Power Query?

[L70F]

Hi, I am new with Power Query and need your help in my project.  

In Excel I have solved this task with Index/Match/If arrow function but I have big difficulty to find some good solution in Power Query.

I have a reference table with the columns: “Com.group”; “Qty Interval”; “Recommended_Qty”

And a workbook with columns: “ItemNo”; “Com.group”; “Annual Demand”.

I need:

For each ItemNo find the closest value below or equal "AnnualDemand" value within "Qty Interval" for right "Com.group". Therefore, pick the value from "Recommended_Qty".

Expected Result:

The workbook should be completed with value “Qty Interval” and “Recommended Qty” for each ItemNo from Reference table.

Reference table
Com.group	Qty interval	Recommended Qty
3	0	1; 2; 5; 10
3	10	5; 10; 20; 30
3	30	20; 30; 50; 100
3	100	Brackets are not applicable!
3	500	Brackets are not applicable!
3	1000	Brackets are not applicable!
4	0	1; 5; 10; 20
4	10	1; 5; 10; 20
4	30	10; 20; 30; 50
4	100	50; 100; 200; 500
4	500	Brackets are not applicable!
4	1000	Brackets are not applicable!
5	0	1; 5; 10; 50
5	10	1; 5; 10; 50
5	30	20; 50; 100; 200
5	100	50; 100; 200; 500
5	500	200; 500; 700; 1000
5	1000	Brackets are not applicable!

	Workbook		Wished result	Wished result
ITEM_NO	Com.group	Annual demand	Qty Interval	Recommended Qty
TE30	3	315	100	Brackets are not applicable!
AG100	3	83	30	20; 30; 50; 100
112	4	16	10	1; 5; 10; 20
705	4	125	100	50; 100; 200; 500
787	5	3000	1000	Brackets are not applicable!
999	5	75	30	20; 50; 100; 200

I really appreciate your soon suggestion for solution since I’m stuck with this for a couple of weeks now.

Thanks a lot.

[Anonymous · Accepted solution]

Hi,

Try this in your power query advanced editor:

let
    Origen = Excel.CurrentWorkbook(){[Name="tWorkbook"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"ITEM_NO", type text}, {"Com.group", Int64.Type}, {"Annual demand", Int64.Type}}),
    #"Consultas combinadas" = Table.NestedJoin(#"Tipo cambiado",{"Com.group"},tReference,{"Com.group"},"tReference",JoinKind.LeftOuter),
    #"Se expandió tReference" = Table.ExpandTableColumn(#"Consultas combinadas", "tReference", {"Qty interval"}, {"Qty interval"}),
    #"Columna condicional agregada" = Table.AddColumn(#"Se expandió tReference", "Personalizado", each if [Qty interval] <= [Annual demand] then 1 else 0),
    #"Filas filtradas" = Table.SelectRows(#"Columna condicional agregada", each ([Personalizado] = 1)),
    #"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas",{"Personalizado"}),
    #"Filas agrupadas" = Table.Group(#"Columnas quitadas", {"ITEM_NO", "Com.group", "Annual demand"}, {{"Qty Interval", each List.Max([Qty interval]), type number}}),
    #"Consultas combinadas1" = Table.NestedJoin(#"Filas agrupadas",{"Com.group", "Qty Interval"},tReference,{"Com.group", "Qty interval"},"tReference",JoinKind.LeftOuter),
    #"Se expandió tReference1" = Table.ExpandTableColumn(#"Consultas combinadas1", "tReference", {"Recommended Qty"}, {"Recommended Qty"})
in
    #"Se expandió tReference1"

Tell us if works for you.

[Ashish_Mathur]

Hi,

Try these calculated column formulas in Table2

Qty interval = CALCULATE(Max(Table1[Qty interval]),FILTER(Table1,Table1[Com.group]=EARLIER(Table2[Com.group])&&Table1[Qty interval]<=EARLIER(Table2[Annual demand])))

Recommended qty = LOOKUPVALUE(Table1[Recommended Qty],Table1[Com.group],Table2[Com.group],Table1[Qty interval],[Qty interval])

Hope this helps.

[L70F]

Hi Ashish, thank you for your suggestion. Unfortunately, I can't get it working. When I copy your formula in the Custom column in Query for Table2 I get the wrong message. What am I doing wrong?

[Ashish_Mathur]

Hi,

You are writing my formula at the wrong place.  Mine is a calculated formula solution - not an M solution.

[L70F]

Hi Ashish, 

where can I found the right place to insert your culculation formula? I have done it by insert "Custom column" and then copy in it your formula. But it didn't work. I tried also with "Invoke custom function", but come nowhere with it. Pehaps you can show some screen shots so I can learn me. 

Thanks a lot. 

Best regards 

Larissa

[Ashish_Mathur]

Hi,

You have to go to Modelling > New calculated column

[Anonymous · Accepted solution]

Hi,

Try this in your power query advanced editor:

let
    Origen = Excel.CurrentWorkbook(){[Name="tWorkbook"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"ITEM_NO", type text}, {"Com.group", Int64.Type}, {"Annual demand", Int64.Type}}),
    #"Consultas combinadas" = Table.NestedJoin(#"Tipo cambiado",{"Com.group"},tReference,{"Com.group"},"tReference",JoinKind.LeftOuter),
    #"Se expandió tReference" = Table.ExpandTableColumn(#"Consultas combinadas", "tReference", {"Qty interval"}, {"Qty interval"}),
    #"Columna condicional agregada" = Table.AddColumn(#"Se expandió tReference", "Personalizado", each if [Qty interval] <= [Annual demand] then 1 else 0),
    #"Filas filtradas" = Table.SelectRows(#"Columna condicional agregada", each ([Personalizado] = 1)),
    #"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas",{"Personalizado"}),
    #"Filas agrupadas" = Table.Group(#"Columnas quitadas", {"ITEM_NO", "Com.group", "Annual demand"}, {{"Qty Interval", each List.Max([Qty interval]), type number}}),
    #"Consultas combinadas1" = Table.NestedJoin(#"Filas agrupadas",{"Com.group", "Qty Interval"},tReference,{"Com.group", "Qty interval"},"tReference",JoinKind.LeftOuter),
    #"Se expandió tReference1" = Table.ExpandTableColumn(#"Consultas combinadas1", "tReference", {"Recommended Qty"}, {"Recommended Qty"})
in
    #"Se expandió tReference1"

Tell us if works for you.

[L70F]

Hi Miltinho,

 I have tried your solution with given example and it works well even when I have a value of “Annual demand”=0.

  

But when I try to apply the same to my project I suddenly stuck on the step “Filas Agrupades” . Please, see the picture below.

 

Do you have any idea what might be the reason of that?

-Different type of "Qty interval", Int64.Type in table tReference, but type number in the function List.Max?

-Might Power Query doesn’t recognize the function of List.Max?

 Thanks for help

[Anonymous · Accepted solution]

Hi L,

I can to replicate the issue. You must have one or more some cells empty that is interpreted by power query as "null".

You have to delete each of their rows or fill each of them with their right information in your source or add other step with what power query should do with those "null" values.

Kind regards.

[L70F]

Thanks Miltinho, I could find the fault and now all work well. 

Best regards

Larissa

[L70F]

Thank you Miltinho

You solution works well. I am happy.