Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shelley
Continued Contributor
Continued Contributor

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

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?
1 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())

5.JPG

Step2:

Use this code to filter data

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

Result:

6.JPG

 

Of course, you could remove Custom column later.

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Shelley
Continued Contributor
Continued Contributor

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.

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())

5.JPG

Step2:

Use this code to filter data

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

Result:

6.JPG

 

Of course, you could remove Custom column later.

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Shelley
Continued Contributor
Continued Contributor

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
Community Champion
Community Champion

@Shelley

 

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

 

Date.StartOfMonth(DateTime.LocalNow())

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.