Forum Discussion

wowcow73's avatar
wowcow73
Copper Contributor
Mar 15, 2019

Date value changes to text on refresh of Power Query data - only for Excel 365

I have an issue where my date columns are transforming to numbers and defined as 'general' when I refresh the data.  I've tried formatting the entire column as date and saving the file.  The next time I open the report and refresh the dates are converted to it's numerical equivalent.  In Power Query, the columns are seen as Date/Time; therefore, I changed it to 'Date' to see if the Time portion could be causing a problem.  That did not help.  I'm at a loss as to what the issue is.  This does not happen with Excel 2013.  I can't find any settings in Excel 365 that might be causing this.

BI & Data Analytics
Power Query

3 Replies

  • Joshua_Wilson's avatar
    Joshua_Wilson
    Copper Contributor
    Oct 04, 2020

    1. Right click on target table(s).

     

    2. Hover on the Table menu,

     

    3. Select External Data Properties

     

    4. Check the box next to "Preserve column/sort/filter/layout"

     

     

     

     

    wowcow73 

     

     

  • Norb_Urban's avatar
    Norb_Urban
    Copper Contributor
    Jan 07, 2020

    wowcow73- I'm experiencing the same issue.  For me, it seems to loose formatting on all 'newly added' records at the bottom of the table.  As an interim solution, I created a macro to use Format Painter to copy the 1st row's format to the rest of the table.

     

    ** Is ANYONE from MICROSOFT monitoring this forum & issue?

Resources