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

%3CLINGO-SUB%20id%3D%22lingo-sub-370118%22%20slang%3D%22en-US%22%3EDate%20value%20changes%20to%20text%20on%20refresh%20of%20Power%20Query%20data%20-%20only%20for%20Excel%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-370118%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20issue%20where%20my%20date%20columns%20are%20transforming%20to%20numbers%20and%20defined%20as%20'general'%20when%20I%20refresh%20the%20data.%26nbsp%3B%20I've%20tried%20formatting%20the%20entire%20column%20as%20date%20and%20saving%20the%20file.%26nbsp%3B%20The%20next%20time%20I%20open%20the%20report%20and%20refresh%20the%20dates%20are%20converted%20to%20it's%20numerical%20equivalent.%26nbsp%3B%20In%20Power%20Query%2C%20the%20columns%20are%20seen%20as%20Date%2FTime%3B%20therefore%2C%20I%20changed%20it%20to%20'Date'%20to%20see%20if%20the%20Time%20portion%20could%20be%20causing%20a%20problem.%26nbsp%3B%20That%20did%20not%20help.%26nbsp%3B%20I'm%20at%20a%20loss%20as%20to%20what%20the%20issue%20is.%26nbsp%3B%20This%20does%20not%20happen%20with%20Excel%202013.%26nbsp%3B%20I%20can't%20find%20any%20settings%20in%20Excel%20365%20that%20might%20be%20causing%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-370118%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1063948%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20value%20changes%20to%20text%20on%20refresh%20of%20Power%20Query%20data%20-%20only%20for%20Excel%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1063948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F274216%22%20target%3D%22_blank%22%3E%40wowcow73%3C%2FA%3E%26nbsp%3BI%20have%20a%20similar%20issue.%20Did%20you%20ever%20work%20out%20a%20solution%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1095225%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20value%20changes%20to%20text%20on%20refresh%20of%20Power%20Query%20data%20-%20only%20for%20Excel%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1095225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F274216%22%20target%3D%22_blank%22%3E%40wowcow73%3C%2FA%3E-%20I'm%20experiencing%20the%20same%20issue.%26nbsp%3B%20For%20me%2C%20it%20seems%20to%20loose%20formatting%20on%20all%20'newly%20added'%20records%20at%20the%20bottom%20of%20the%20table.%26nbsp%3B%20As%20an%20interim%20solution%2C%20I%20created%20a%20macro%20to%20use%20Format%20Painter%20to%20copy%20the%201st%20row's%20format%20to%20the%20rest%20of%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E**%20Is%20ANYONE%20from%20MICROSOFT%20monitoring%20this%20forum%20%26amp%3B%20issue%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1743370%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20value%20changes%20to%20text%20on%20refresh%20of%20Power%20Query%20data%20-%20only%20for%20Excel%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1743370%22%20slang%3D%22en-US%22%3E%3CP%3E1.%20Right%20click%20on%20target%20table(s).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Hover%20on%20the%20Table%20menu%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20Select%20External%20Data%20Properties%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E4.%20Check%20the%20box%20next%20to%20%22Preserve%20column%2Fsort%2Ffilter%2Flayout%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F274216%22%20target%3D%22_blank%22%3E%40wowcow73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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.

3 Replies
Highlighted

@wowcow73 I have a similar issue. Did you ever work out a solution?

Highlighted

@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?

Highlighted

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