How to get numeric values to display as date in Power Query?

[jplank]

I am using Power Query and I have a column of dates that's showing up in numeric format. i.e. the values are like "42738" instead of "Jan 4, 2017". How would I go about getting them to display as a date? When I change the type to Date, I get an error.

 

[sandy666]

I think you should read all from here first

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[jplank]

Thanks, I've actually already had a look at many of those links. Is there a part in there that you think would help with the issue I'm having?

 

[sandy666]

with you description I don't know the number you show is a text or as number
so you can try change the text-number to the whole number then to date (I assume there is a date only not date time)
check the length, if there is more than 5 you have any hidden character

 

[sandy666]

Is there a part in there that you think would help with the issue I'm having?

The fourth link gives you access to all Power Query functions

 

[anvg]

Hi
Try transformate column with number as text and number as number to date

let
    Source = #table({"date"}, {{"42738"}, {42738}, {"abcd"}}),
    toDate = Table.TransformColumns(Source, {{"date", each try Date.From(Number.From(_)) otherwise _, Date.Type}})
in
    toDate

if you need not non transformable value in output then replace from otherwise _ to otherwise null
Regards,