excel-2013powerquery

Power query conditional max min date


I'm using PowerQuery 2013, and I'm new to it.

Here is a sample of my data:

need to remove yellow rows

I need to crop the dates in Power Query, on market level, to include only overlapping range of dates (can't just delete rows with nulls - it's OK to have nulls inside data range).

I have found a number of articles to use functions but I'm just not good enough to adapt them to my situation.

I tried to create a function (called MaxDate) and then create a column in my data invoking this function:

MaxDate function query:

(table as table, mkt as text) => Table.Max(Table.SelectRows(table, [Market]=mkt))[Date]

Invoke the function in a new column:

= Table.AddColumn(Sales, "GetMaxDate", each MaxDate(Sales, [Market])[Date])

This returned "Error" and I can't resolve it. I was going to generate min/max date columns, and then filter out dates that fell outside as a final output.


Solution

  • Try adjusting the syntax like this:

    (table as table, mkt as text) => Table.Max(Table.SelectRows(table, each [Market]=mkt), "Date")