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.
Try adjusting the syntax like this:
(table as table, mkt as text) => Table.Max(Table.SelectRows(table, each [Market]=mkt), "Date")