Forum Discussion
How to apply Index/Match/If for array function in Power Query?
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.
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.
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.
10 Replies
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.
Thank you Miltinho :smileyhappy:
You solution works well. I am happy. :smileyhappy:
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
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.
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.
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?
Hi,
You are writing my formula at the wrong place. Mine is a calculated formula solution - not an M solution.
