March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
Solved! Go to 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.
In Addition: I believe that the above error leads onto this error:
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.
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
86 | |
82 | |
76 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |