type datetime - after transformation in Excel in Num-Format

[n8schicht]

Hello Everybody,

i have a problem:

#"Geänderter Typ1" = Table.TransformColumnTypes(sw_az_update,{{"sw_az_update", type datetime}}),

after Transformation, the format in the excel sheet looks like: 

43693,01013

how can i format this in date-format - like in the powerquery showed?powerqery for date: =DateTime.LocalNow()Unfortunately in Excel, the date is then displayed incorrectly

thx

[Stachu]

this is an Excel question, so for the future I'd suggest using the Excel forums:
https://social.technet.microsoft.com/forums/office/en-us/home?forum=excel&filter=alltypes&sort=lastp...

Power Query can only have 1 datatype in the column, and it seems you have multiple (boolean, datetime, integer, decimal), as the result of unpivotting. Still Power Query has to assign single data type to the unpivotted column. I assume the type at the moment is either Any or Decimal, that's why the formatting is not preserved.

You can just change the format of the cell with 'Ctrl + 1', but I think you would need to do that each time
Alternatively you could have a helper column specyfying the type of particular row, and then change the type to Text with specific formatting