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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
namitsamal
Frequent Visitor

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 ?

1 ACCEPTED SOLUTION

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

12 REPLIES 12
lndnbrg
Resolver III
Resolver III

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

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.

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.

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.

Hi @namitsamal,

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

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.

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.

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?

Thank you, it was indeed a date conversion issue. I was able identify it by using a different date format in the source excel file. The second step was to avoid any conversion when the file is imported in PowerBI desktop "Edit Query". I am able to view the data in the PowerBI service. PowerBI desktop does not recognise this universal date format as date when imported, if I change it to date in "Edit Query" and publish the dashboard the problem persists. What should I do ?

I would try to change the data type by setting the locale explicitly:

In Power Query Editor, right-click on the column header / "Change Type" / "Using Locale..." / then choose Date format with the locale you want

source:
https://community.powerbi.com/t5/Desktop/date-format-is-changed-in-power-bi-desktop/m-p/45385#M17618

Ok, I'll try.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors