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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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