cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mega79 Regular Visitor
Regular Visitor

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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Dates disappear when Refreshing data in Power BI Desktop

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
Moderator v-yuezhe-msft
Moderator

Re: Dates disappear when Refreshing data in Power BI Desktop

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.
Mega79 Regular Visitor
Regular Visitor

Re: Dates disappear when Refreshing data in Power BI Desktop

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

Moderator v-yuezhe-msft
Moderator

Re: Dates disappear when Refreshing data in Power BI Desktop

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.
Mega79 Regular Visitor
Regular Visitor

Re: Dates disappear when Refreshing data in Power BI Desktop

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

Moderator v-yuezhe-msft
Moderator

Re: Dates disappear when Refreshing data in Power BI Desktop

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.
Mega79 Regular Visitor
Regular Visitor

Re: Dates disappear when Refreshing data in Power BI Desktop

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?

Moderator v-yuezhe-msft
Moderator

Re: Dates disappear when Refreshing data in Power BI Desktop

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

Mega79 Regular Visitor
Regular Visitor

Re: Dates disappear when Refreshing data in Power BI Desktop

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!

Highlighted
MyraMa Regular Visitor
Regular Visitor

Re: Dates disappear when Refreshing data in Power BI Desktop

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

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 1,850 guests
Please welcome our newest community members: