cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Dates not being recognised correctly in Power BI

Hi folks, 

 

 

I have hit a deadend here while trying to make Power BI (PBI) recognise the dates, and its recognising some, and throwing error for some, specifically, where ever the date is greater than 12. 

 

Dates and errorDates and error

 Here is the full scenario;

I am importing dataset from a SSRS report into PBI using the Desktop application. In the SSRS report, I have set report locale to en-AU.

Locale of SSRS reportLocale of SSRS report

And, have explicitly set the locale, and formatting for the date field

Locale and Format of date field/columnLocale and Format of date field/column

I am importing this particular report as a CSV file into Power BI

Importing from SSRS into PBI as CSVImporting from SSRS into PBI as CSV

I am hoping to resolve this in a way where I can get PBI Desktop to recognise the dates without having to implement any custom DAX and/or M formula, simply to keep the simplicity of importing data into PBI & PQ intact. 

 

Help please!

 

Thanks

Kaz

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Dates not being recognised correctly in Power BI

Hi @krajani,

 

Based on my understanding, you have created a SSRS report and export it to a CSV file. Then you get data from this CSV file in Power BI Desktop, when you convert the [InvoiceDate] values to Date type, the error displays when the first part of the date value is grater than 12, right?

 

Based on my test, the issue can be caused by that the Local for the Power BI Desktop is not corresponding to the converted date value. Suppose the Local is English( United States), the valid date format is MM/dd/yyyy. If the value is 27/06/2014, when it's converted to date type value, as there is no month "27", the error throws out. In your scenario, please change Local value as English(Australia) then click Refresh Preview button in Query Editor.

 

w2.PNGw1.PNG

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

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

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Dates not being recognised correctly in Power BI

Hi @krajani,

 

Based on my understanding, you have created a SSRS report and export it to a CSV file. Then you get data from this CSV file in Power BI Desktop, when you convert the [InvoiceDate] values to Date type, the error displays when the first part of the date value is grater than 12, right?

 

Based on my test, the issue can be caused by that the Local for the Power BI Desktop is not corresponding to the converted date value. Suppose the Local is English( United States), the valid date format is MM/dd/yyyy. If the value is 27/06/2014, when it's converted to date type value, as there is no month "27", the error throws out. In your scenario, please change Local value as English(Australia) then click Refresh Preview button in Query Editor.

 

w2.PNGw1.PNG

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

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

View solution in original post

Highlighted
Helper V
Helper V

Re: Dates not being recognised correctly in Power BI

Thanks @v-qiuyu-msft. For some strange reason, PBI Desktop decided the change the regional setting for this particular PBIX file to English US. If I open a new PBI Deskop instance, its set to English Australia, but opening this particular file changes it back to English US. Changing the Locale back to English Australia explicitly, and then doing the refresh doesn't give me the dates error. 


Thanks for your help, appreciate.

 

Kaz

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors