How to Calculate the first day of the Month in Power Query

[Shelley]

Hi All, I'm sure I'm missing some syntax and just can't get this right. I want to bring in all the records with an order submit date < the first date of the current month. #"Filtered Rows1" = Table.SelectRows(#"Replaced Errors1", each [Order_Submit_Date] < Date.StartOfMonth(#date(DateTime.Date(DateTime.LocalNow())))) I'm receiving an error "OLE DB or ODBC error: {Expression.Error] 1 arguments were passed to a function which expects 3.. What the heck am I doing wrong?

[Shelley]

Okay, have it running with no error, but it's not working correctly. #"Filtered Rows1" = Table.SelectRows(#"Replaced Errors1", each [Order_Submit_Date] < #date(Date.StartOfMonth(#date(DateTime.Date(DateTime.LocalNow()) )))) Gives me everything < 1/7/2019. I want < 1/1/2019.

[v-lili6-msft · Accepted solution]

hi, @Shelley

You may try to divide your #"Filtered Rows1" into two steps, this will be a simple issue.

Step1:

Add a first day of the Month column and change the type of it to date

=Date.StartOfMonth(DateTime.LocalNow())

Step2:

Use this code to filter data

= Table.SelectRows(#"Changed Type1", each [Date] < [Custom])

Result:

Of course, you could remove Custom column later.

Best Regards,

Lin

[Shelley]

This appears to work, Lin. The only thing is that it seems to run super slow, but I don't know if it has to do with this script or if its our network or server or something else. Thanks for your help!

[Zubair_Muhammad]

@Shelley

what if you use this. No need for #date i think

Date.StartOfMonth(DateTime.LocalNow())