Conditional Max in Excel Power Query M language?

Question

I am facing the issue of reusing neat Excel formula for MAXIFs in Power Query M language. The formula itself consists of several conditions regarding columns in Table2 and a value of interest (VOI) in Table1 (both being Excel table objects).

formula in Table1:

=MAXIFS(Table2[columnA],Table2[columnB],"criteriaB1",Table2[columnC],[@[VOI]],Table2[columnA],"<="&MINIFS(Table2[columnA],Table2[columnB],"criteriaB2",Table2[columnC],[@[VOI]])

(I will divide the formulas into lines to make reading easier)

=MAXIFS(Table2[columnA],
        Table2[columnB],"criteriaB1",
        Table2[columnC],[@[VOI]],
        Table2[columnA],"<="&MINIFS(Table2[columnA],
                                    Table2[columnB],"criteriaB2",
                                    Table2[columnC],[@[VOI]])

So far I've been trying merging Table1 with Table2, grouping by some of the columns but as result I receive chunks of data that I can't/don't know how utilize in next steps. I simply cannot see the complete landscape of the procedure in Power Query M language.

Any help would be appreciated.

Alexis Olson Alexis Olson · Accepted Answer · 2020-03-18T14:43:37

The corresponding idea would be to take a maximum over a filtered table.

For example, the MINIFS part would look roughly like this:

MinA =
List.Min(
    Table.SelectRows(
        Table2, each [ColumnB] = "criteriaB2" and [ColumnC] = "VOI"
    )[ColumnA]
)

It gets a bit trickier since you need to pass the current row value of Table1[VOI] into the second condition but it's still doable and might look something like this:

AddMinAColumnToTable1 =
Table.AddColumn(
    Table1, "MinA",
    (Tab1Row) =>
        List.Min(
            Table.SelectRows(
                Table2, each [ColumnB] = "criteriaB2" and [ColumnC] = Tab1Row[VOI]
            )[ColumnA]
        )
)

I recommend reading this blog post for a better understanding of the each and (_) => constructions.

Conditional Max in Excel Power Query M language?

1 Answers