Query "modified Date" error

[Anonymous]

Hi All,

I have a powerbi file that I has been running correctly for the last 3 months but when I updated this month I started to get a date error in the query:

The actual error message is:

The Excel files are held on a SharePoint site... I have checked the data in the file and it seems to be OK... no different to the other files that do not have the error.

From searches I found one work around which is to change the time format of the column to local; I tried this but it does not resolve the issue:

I have to present this PBI to 300 people this afternoon so if anybody has any ideas please let me know.

[Anonymous]

In Addition: I believe that the above error leads onto this error:

[Gordonlilj]

Hi,

The error is probably that power bi is trying to parse the value 21 as a month.

One solution could be by following the solution here but with a few changes.

So first split the column

Use "/" as your delimiter

Now you should have three columns, one containing days, one containing months and one containing year and time. Split the year/time column with space as your delimiter giving you a total of 4 columns.

It's a good idea to rename the columns to something like, Year,Month,Day and Time so you'll know which is which

If your new column containing your time data is a time datatype, change it to text.

Now create a custom column using this code

Text.Combine({Number.ToText([Year]),Number.ToText([Month]),Number.ToText([Day])},"/") & " " & [Time]

Lastly change it to a datetime and it should work.

[Anonymous]

Hi Gordon, Thank you for the feedback! Unfortunately the date field is used in several queries and a bridging query so i would have to basically rebuild the model to test your solution. I can't do that now because I am in the middle of a seminar.

[Anonymous · Accepted solution]

So.... the issue was the date format and I had been trying to correct that error by setting the date local on that column. What I didn't realize in the beginning was that I had a second date column with the same issue. By setting both date columns to date locale at the same time enabled the query to remove the error.