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
Mega79
Helper I
Helper I

Dates disappear when Refreshing data in Power BI Desktop

Hi everyone! I'm having problems with some Reports that I want to Refresh at least 4 times a month.

 

The data comes from one Excel database downloaded from an intranet portal. This files have at least 10 000 lines.

I insert more data into this Excel database every week before using Power BI. Once I have all new data inside the Excel database I then open Power BI Desktop and hit the Refresh botton to update with the new data.

 

Here's the problem: every time I hit the Refresh data, all of my "Date" type columns go blank... I do not know why this happens all the time but apparently I'm doing something wrong.

 

Can anyone please help me with this?

 

Thanks in advance for your help.

1 ACCEPTED SOLUTION

Hi @Mega79,

I can reproduce the above error when refreshing data in Data view.

However, when I save the Excel file as a .xlsx file rather than .xlsb file, import the .xlsx file into Power BI Desktop, add additional records in the .xlsx file and refresh data in Power BI, everything works well. Could you please test if the process works in your Power BI Desktop? If it works, please recreate reports after importing the .xlsx file into Power BI Desktop.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
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

9 REPLIES 9
v-yuezhe-msft
Employee
Employee

Hi @Mega79,

I test your Excel file in Power BI Desktop. When I import the data from Excel to Power BI, the data with Date/DateTime type in Excel are recognized with Text type. And when I try to change the type of Date columns from Text type to Date/DateTime type in Query Editor, I will get the “We couldn't parse the input provided as a Date value” error message or “We couldn't parse the input provided as a DateTime value”. Then the Date columns will be filled with Error, in this case, after I apply the changes, the Date columns will go blank in Data View as shown in the following screenshot.
Capture111.PNG

 

In your scenario, firstly, please define the two Date columns in Excel to Date and DateTime type, make sure that you follow the instructions in this similar blog to verify that these data are really defined with Date format rather than Text format in Excel.

Secondly, the date data type/format is controlled by the Locale Setting in Power BI Desktop, change the Locale setting to match the source date format following the instructions in this similar thread, then check if you can successfully refresh data.


Thanks,
Lydia Zhang

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

Hi Lydia, thanks for your update. I have done everything you said in your last message and I get the following message.

 

 

 

Capture.PNG

 

I have tried changing the Locale settings eather to English and Spanish and nothing is working. There is also another column with dates and I changed that one as well to date... but still I keep getting eather the above message or blank cells.

What else can I do?

Hi @Mega79,

I can reproduce the above error when refreshing data in Data view.

However, when I save the Excel file as a .xlsx file rather than .xlsb file, import the .xlsx file into Power BI Desktop, add additional records in the .xlsx file and refresh data in Power BI, everything works well. Could you please test if the process works in your Power BI Desktop? If it works, please recreate reports after importing the .xlsx file into Power BI Desktop.

Thanks,
Lydia Zhang

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

Thanks so much for your help Lydia!

You were right about the binary file. I changed the entire database into .xlsx and everything works perfectly.

 

Thanks again!

Hi @v-yuezhe-msftLydia,

 

I got the similar problem. I've done what you mentioned (check date type at both places), my file is already a .xlsx file. It doesn't show date at all when I load excel data into Power BI. Could you please help?

 

My excel file is shared here: 

https://radacad-my.sharepoint.com/personal/myra_ma_radacad_com/_layouts/15/guestaccess.aspx?docid=12...

 

 

Really appreciate your help.

Myra

 

v-yuezhe-msft
Employee
Employee

Hi @Mega79,

I am not able to reproduce the issue when refreshing data of Excel file in Power BI Desktop.

Could you please share the Excel file to me? I will test it in my environment, and please describe more details about that what new data you insert into the Excel file.

Thanks,
Lydia Zhang

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

Hi Lydia, I'm new at this. Could you please tell me how I can share the file with you?

Hi @Mega79,

You can upload the Excel file to OneDrive and share the link here.


Thanks,
Lydia Zhang

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

Hi Lydia, here is the lynk of the Excel File:

https://1drv.ms/x/s!ArGHkKvS5b0KbJ1_WA_1xOS_l6g

 

And here is the lynk to the Power BI Desktop report that doen't refresh correctly the data:

 

https://1drv.ms/u/s!ArGHkKvS5b0KbcYbVHXYQ7DvMn8

 

Thanks in advance for your help

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.