Combine Multiple Files from Folder by Create Date Logic

[chudson]

Hi,

I'm trying to combine multiple files from a folder that are each the first file created in each month.  I know how to combine the most recent file, a range of files and filter specfic dates but I don't know how to automatically create logic to combine and pull in the first file created on every month.  

Scenario:  My Create Date column ranges from 1/1/2019-11/18/2019 right now.  I want to only pull in 1/1/2019, 2/1/2019, 3/1/2019, 4/1/2019 or whatever is the first created date of the month.  One other kicker is they are only generated Monday's through Friday's so in June the first of the month's would be 6/3/2019. 

Does anyone know logic or how to combine these files without me having to come in an filter to bring in the first month's file each month?

Thanks,

Chris

[Wkeith]

Interesting, I need to do the same thing. Hopefully someone can help us out! 

[d_gosbell · Accepted solution]

So i think the trick here is to figure out if a given date is the first week day of the month, then we can filter down to just those.

To do that you can use the following M function. In a nutshell it takes a date as a parameter, generates the first 3 days of the month, then excludes any Sat/Sun dates and returns the min date from what is left. To add this to your Power BI file open the query editor, click on "New Source" and choose "Blank Query" then click on "Advanced Editor" in the ribbon and paste the following code in.

= (theDate as date ) as logical =>
let
  firstDateInMonth = #date(Date.Year(theDate), Date.Month(theDate), 1),
  first3dates = List.Generate(()=>firstDateInMonth, each Date.Day(_) <= 3, each Date.AddDays(_, 1)),
  weekDaysInList = List.Select(first3dates, each Date.DayOfWeek(_,Day.Saturday) > 1),
  firstWeekDay = List.Min(weekDaysInList),
  result = firstWeekDay = theDate 
in
  result

In my test file I then called this query "fnIsFirstWeekDayInMonth"

Then I entered a series of test dates (note that my PC is using a dd/MM/yyyy date format) and added a column invoking a custom function and used the function above. That gives an output like the following

we can then filter down to just the true values

You should be able to apply this to the list of files in your folder to only get those created on the first week day of the month

[chudson]

@d_gosbell , thank you very much.  Your method worked!