cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RV1
Helper I
Helper I

Today's date in power query

Hi ,

 

I am trying to pick up records where the TE_To date is < today's date. Here is the script.

 

    #"Filtered Rows" = Table.SelectRows(prorm_timeentries_table, each [prorm_to] < DateTime.Date(DateTime.LocalNow()))
in
    #"Filtered Rows"

 

I get an error message as follows:

Expression.Error: We cannot apply operator < to types Date and DateTimeZone.
Details:
    Operator=<
    Left=17/01/2019
    Right=6/07/2014 5:00:03 AM +00:00

 

What am I doing wrong? How do I convert DateTime.LocalNow() to just date?

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @RV1

I use your formula successfully.

Please check if your [prorm_to] column is of Date type.

1.png

2.png

 

Best Regards
Maggie



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

 

Hi Maggie .. Thanks for the response. Yes my field is of date type. I noticed in the error message it wsa showing a comparison between a date to date-time-zone data-type. I changed the function accordingly and still got an error.

v-juanli-msft
Community Support
Community Support

Hi @RV1

After clicking on "replace current" button, i can modify the error.

3.png

 

If you have applied many steps in "prorm_timeentries_table" after "Changed type" step, please insert the step "change "datetime/zone" to "date" after the "changed type" step.

4.png

 

if your problem doesn't solve, please share the whole code in the Advanced editor of the "prorm_timeentries_table", i am willing to work on this problem.

 

Best Regards

Maggie

 

Hi Maggie,

 

We have developed a rash of issues in last few days. Our reports were working perfectly and getting refreshed regularly through scheduled refresh. All of a suddent multiple issues have cropped up. I am not sure if they are linked but they are cropping in the reports that are linked to Dynamics 365.

 

1. We upload time entries from 1/7/2018 to today. The system refused to accept the filter and kept showing the To_Date value in date-time-zone format even though the field was date type only.

 

2. One of the fields has blanked out completely. The data was coming from 2 levels of lookup. TimeEntry->Plan->Contract. Apparantly, this level of lookups are suddenly not in favour. They were fine until Jan 10th.

 

3. Some tables from D365 are throwing up error -

OData: Request failed: The remote server returned an error: (400) Bad Request. (Number of calculated fields in query exceeded maximum limit of 10.)

This has through our entire reporting framework go haywire. There is no problem with data that is coming from SQL Server.

Any help is appreciated.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.