Column with number/text for date

[diegolima]

Hello guys, how are you? I need help, I have a table and I need to change a column of number/text for date but when I try to change a error is showing:

Can someone help me?

Thank you all!

[nandic]

There is a problem as data is integer type in format yyyymmdd, Power Query can't recognize it as date.
I added new column using this formula:
Date.FromText(Text.Start(Number.ToText([Date Key]),4) & "-" & Text.Range(Number.ToText([Date Key]),4,2) & "-" & Text.End(Number.ToText([Date Key]),2))
After that, just change type of new column to date.

Example:

[v-alq-msft]

Hi, @diegolima 

Power query date format is based on your system region setting, you can't manually modify it and keep date type in power query side. I'd like to suggest you refer to the similar thread to see if it helps.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @diegolima ,

you should change column type from number to text and then change to date.

To give an idea have a look here:

let
    Origine = Excel.Workbook(File.Contents("C:\Users\abcdef\OneDrive - TIM\MyD2020\BI\date from number.xlsx"), null, true),
    Tabella2_Table = Origine{[Item="Tabella2",Kind="Table"]}[Data],
    #"Modificato tipo" = Table.TransformColumnTypes(Tabella2_Table,{{"Date", type text}, {"Country", Int64.Type}}),
    toDate=Table.TransformColumns(#"Modificato tipo", {"Date", Date.FromText})

in
    toDate

[diegolima]

HI @Anonymous 

I tried to change it to text first, but it didn't work:

Do you know what else I can do?

Thanks for all

[Anonymous]

Hi @diegolima 

You are trying to change column type, the solution from @Anonymous assumed transforming the content of the column.

You need to first change the type to Text, on the next step you need to transform the data using Data.From. If this does not work adapt the second step to something like this.

= Table.TransformColumns(Source, {{"Column1", each Date.FromText(_, "en-GB"), type date}})

The solution from  @nandic should also work, so you can use it as well.

Kind regards,

JB

[Anonymous]

in my environment:

try using:

     Table.TransformColumns(#"Modificato tipo", {"Date", each Date.FromText(_,"it-IT")})
in
    toDate

[artemus]

Might be a locale issue. Try using the bottom option of "using locale" and choose something like pt-br