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.