Reply
Frequent Visitor
Posts: 10
Registered: ‎12-01-2017
Accepted Solution

Blank dates in PowerBI report

Hi,

 

I am refreshing my report in PowerBI service via access to an underlying sharepoint data. The data set gets refreshed with the latest data but does not pull date fields. When the same report is opened in PowerBI desktop and refreshed the dates appear Ok.

 

Any solutions ?


Accepted Solutions
Highlighted
Member
Posts: 48
Registered: ‎03-02-2016

Re: Blank dates in PowerBI report

Can you test if your date column has errors that will be saved as blanks during the Power BI service refresh?

It may be a date conversion issue, because your Desktop uses a different locale than the service. You may try to do the date conversion with a specific locale. Check this: https://www.excelguru.ca/blog/2015/07/08/fix-date-errors/

Maybe this leads you to a solution?

View solution in original post


All Replies
Member
Posts: 48
Registered: ‎03-02-2016

Re: Blank dates in PowerBI report

Do you both use the latest version of Power Bi Desktop and Data Gateway?

If the version of the Data Gateway is somewhat older, it may behave differently than Desktop.

- Andreas
Frequent Visitor
Posts: 10
Registered: ‎12-01-2017

Re: Blank dates in PowerBI report

Yes, I use the latest versions. When I publish from desktop to service the dates appear. However on first refresh from service everything is updated apart from date columns.

Member
Posts: 48
Registered: ‎03-02-2016

Re: Blank dates in PowerBI report

Does it also happen if you just import the raw SharePoint data - with just the Source step in your Query without any type conversions. Just to avoid any date format related issues.
Frequent Visitor
Posts: 10
Registered: ‎12-01-2017

Re: Blank dates in PowerBI report

Yes, I have tried that as well. The column in the source excel file is also a custom formatted date column. I imported it and published to service. Again on first refresh from service the column turned blank.
Member
Posts: 48
Registered: ‎03-02-2016

Re: Blank dates in PowerBI report

Have you tried a different excel file format? Does it change things?
Does it recognize the column as a date column right away? What it you put some text in the first row in the excel file? Does it at least recognize the column as a text column?

In one case I could fix thinks by copying the contents to a new worksheet.

If you can share a link, I can check it with my Power BI tenant as well.
Super Contributor
Posts: 4,812
Registered: ‎08-14-2016

Re: Blank dates in PowerBI report

Hi @namitsamal,

 

Can you share more detail info about create custom date column? It will be help for troubleshooting.

 

Regards,

Xiaoxin Sheng

Frequent Visitor
Posts: 10
Registered: ‎12-01-2017

Re: Blank dates in PowerBI report

The column is marked as a date time column in the source excel file. On import to PowerBI desktop the display format is changed as per requirements. When refreshed from the desktop the dashboard retrieves the latest data. When the dashoboard is published to PowerBI service and refreshed all the data is retrieved apart from those date columns.

Frequent Visitor
Posts: 10
Registered: ‎12-01-2017

Re: Blank dates in PowerBI report

I cannot use the old compatible excel format (.xls) as it requires me to install a driver on local machine since 64 bit latest PowerBI installation does not support that format. Everthing works fine when dashboard is refreshed from desktop but the columns are not retrieved when refreshed from PowerBI service. I cannot share the file or link as it contains client sensitive data.

Highlighted
Member
Posts: 48
Registered: ‎03-02-2016

Re: Blank dates in PowerBI report

Can you test if your date column has errors that will be saved as blanks during the Power BI service refresh?

It may be a date conversion issue, because your Desktop uses a different locale than the service. You may try to do the date conversion with a specific locale. Check this: https://www.excelguru.ca/blog/2015/07/08/fix-date-errors/

Maybe this leads you to a solution?