cancel
Showing results for 
Search instead for 
Did you mean: 

Dataflow (preview) - dd.mm.yyyy becomes mm.dd.yyyy when consuming data in PBI Desktop

Hi, I am trying dataflows for the first time. Greate feautre by the way! Here is my issue:

 

When creating an entity in dataflow all looks well:

 

image.png


Note the dateformat is dd.mm.yyyy.

 

When trying to consume the same data fra PBI Desktop the date is changed to mm.dd.yyyy and the import is not correct.

 

This is the same data in Power BI Desktop:

 

image.png

 

Note that SID 02/101 has swapped the dd with mm, and that SID 02/1015 failes because month 18 don't exists.

 

I guess this is a locale/language issue, but I cant find out where to change it.

 

Pls advice!

BR
Trond Erik Bones

Status: New
Comments
Frequent Visitor

A way to work around the issue for now. Not great but it works: Use Power Query to extract Year, Month and Day (Possibly time as well) as numeric values in separate columns. Then create the connection to the dataflow in Desktop, go to Power Query and rebuild the date. Until MS fixes the issue, this works. 

Member

I would imagine another, not perfect workaround is converting to text and then converting back. This might a easier than seperating to Year, Month, Day.

 

Looking forward to when this is resolved as this stops me being able to use dataflows as the majority of my tables all have dates in them.

Member

@sagivhThis issue was resolved for Australian dates but is now back as of today. Can you please ask the team to look at this again. I am back to Dataflows not being able to refresh because of this error. 

 

Error: We couldn't parse the input provided as a Date value.

 

Frequent Visitor

Hi,

 

The document locale should be set in the authoring experience, where you create and edit your queries.

The locale is one for all queries in your dataflow.

Can you please set it, save your dataflow and refresh ?

locale.png

 

Thanks,

Michael

Member
@ Michael7 thanks for the tip. I can confirm that the document locale is set to Australia for me but I am still getting the date issue. The problem was resolved until yesterday when it came back again. Regards.
Frequent Visitor

Is there a chance that your data is indeed not in a date format as the error suggests ?


Can you please provide more details such that i will be able to investigate it ? such as failing activity id, etc...

Once, you consume the data in PBI desktop, i suspect that PBI desktop and your operating system should be in a desired locale as well.pbi deksop 2.pngop.png

 

Member
Hi, The response that Michael7 is providing is more likely to by the response that is more useful. However, I was having some dialogue with another Microsoft member and I managed to get the issue resolved for myself, so might be useful for you. What I had was that the DataFlow I was using was created prior to the fix to resolve this, and therefore the Raw CSVs that are created by DataFlows had built this error into the CSVs. The resolution that fixed this was rebuilding the dataflow with the issue (following Michael7's locale guidance) and then connecting to the new DF.
Member

@Michael7 Thanks for coming back to me. The data is text and in the format of 1/2/2017 but when converted a to date in the Dataflow ends up being shown as 2/1/2017. It was all working fine after the fix but they broke again just recently. I can send you some more details via PM. I have now changed everything around so that all dates are text in the Dataflow and are converted to dates in Power BI which works fine. Certainly, all my region settings are correct and have not changed. 

Frequent Visitor

Hi, I can confirm that this issue is resolved for Norwegian Smiley Happy

 

BR
Trond E

RGI Regular Visitor
Regular Visitor

This issue has not been resolved for me (UK).

Is the fix still on the way?