Today's date in power query

[RV1]

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?

[v-juanli-msft]

Hi @RV1

I use your formula successfully.

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

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.
“

[RV1]

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]

Hi @RV1

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

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.

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

[RV1]

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.