Reputation: 482
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.
Upvotes: 1
Views: 2241
Reputation: 40204
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.
Upvotes: 1