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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Query "modified Date" error

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:Date Error.png

The actual error message is:

Date Error 2.png

 

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:

Date Error 3.png

 

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

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

Date Error 4.png

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

 

Capture.PNG

 

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
Not applicable

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
Not applicable

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.