Filter for the earliest date within each month from a list of non-consecutive dates

Power Query
1

Hi Everyone,

I’ve run into a bit of a snag where I have a table of data that gets updated most days, and I need to calculate the value of stock on the first of every month. The table has a “DATE_UPDATED” column so I figured I should be able to filter this column for every Day that equals 1. Simple. However…

There are dates missing as file where the data is captured from isn’t captured every day (remember I said “most days”. There is data from every month since January 2021, the trouble is not every month has data on the first of the month. In the sample file, I’ve just selected two items that illustrate this. The result I get is:

image

As you can see, there are clearly missing months. I need to filter for the first date in each month from the DATE_UPDATED column and just bring that data in, which is why I need to do this in Power Query. Otherwise there will be millions of rows of unnecessary data.

Currently I have this to filter rows:

= Table.SelectRows(#“Sorted Rows”, each Date.Day([DATE_UPDATED]) = 1 )

It did its job, but obviously I’m missing months. I’m guessing what I need in place of the 1 here is some kind of Min function, but I am unsure how to find the Min day for each month shown.

Please help!

Thanks in advance.

Mark D
SLOBs sample.pbix (105.7 KB)

2

Hi @ZENMarkD,

Here’s an approach for you.

  1. Add a Start of Month column. Select your Date column, go to the Add Column tab: From Date & Time / Date / Month / Start of Month
  2. Select the Start of Month column and go to Group By / Advanced / Min over your Date column and add an aggregation to bring in all rows as well
    image
    image711×762 20.3 KB
  3. Expand the AllRows table to bring back additional fields.

I hope this is helpful.

3

Thanks so much Melissa. I think that’s done the trick. In addition to the above I added a custom column that compared the First Date column to the main Date column so I can filter out the rows that don’t match.
I’m having some data issues at the moment that is preventing me from confirming this, but it looks to have worked in Power Query so I can tentatively say it’s solved!
Thanks again!

4

I can now confirm this solution worked!