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 l...
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

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

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

        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!