Forum Discussion

Mega79's avatar
Mega79
Icon for Helper I rankHelper I
10 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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

    • Mega79's avatar
      Mega79
      Icon for Helper I rankHelper I
      10 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Hi Mega79,

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


        Thanks,
        Lydia Zhang

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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.

     

    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

    • Mega79's avatar
      Mega79
      Icon for Helper I rankHelper I
      10 years ago

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

       

       

       

       

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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