Forum Discussion
How to apply Index/Match/If for array function in Power Query?
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.
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 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.
Thanks Miltinho, I could find the fault and now all work well. :smileyhappy:
Best regards
Larissa