Power Query: How to filter in a List.Max function to get maximum from only part of the data

[Anonymous]

Hello, I would like to filter the list of values before I get the max value.

I have 2 tables:

• date dimension with months of current Fiscal Year
• fact / transaction table with actual results and forecasts

In Power Query I would like to add a custom column to the date dimension table with the maximum Month available for "Actual" Scenario from the fact table.

This syntax:  List.Max(#"Actuals Consolidation"[Month])

allowed me to add the maximum Month of the whole fact table table ~ 10/01/2023.

How can I add the filtering to the "Actual" Scenario, please?

When I try something like this:

= Table.AddColumn(#"Added Custom", "Custom.1", each List.Max(Table.SelectRows(#"Actuals Consolidation"[Month], each [Scenario] = "Actual")))

I get error that list cannot be converted to a table.

[lbendlin · Accepted solution]

I would suggest to do that in Power BI DAX instead. List.Max is a potentially very expensive operation that may break folding.

if you must - specify the column name at the end.

= Table.AddColumn(#"Added Custom", "Custom.1", each List.Max(Table.SelectRows(#"Actuals Consolidation", each [Scenario] = "Actual")[Month]))