Power query conditional max min date

Question

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

Here is a sample of my data:

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.

Answer 1

Try adjusting the syntax like this:

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

Answer 2

OK take 2 ...

I think you may be overcomplicating it. With the data in the shape you indicated, I would start a new Query referring to that dataset. I would then Group By [Market] and aggregate the Max and Min of [Date]. I would set that Query to Load To = Only Create Connection.

Then I would start a new Query by reference to the original dataset. I would use a Merge to join it to the Max/Min Query just created, joining on [Market]. Then I would Add a Calculated filter column e.g.

if [Date] = [Min Date] or [Date] = [Max Date]

Finally I would filter on the added column - keeping the FALSE values.