Column with number/text for date

Question

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:&nbsp;&nbsp;Can someone help me?Thank you all!

nandic · Answer

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:

anonymous · Answer

Hi diegolima ,you should change column type from number to text and then change to date.&nbsp;To give an idea have a look here:&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

diegolima · Answer

HI&nbsp;Anonymous&nbsp;&nbsp;I tried to change it to text first, but it didn't work:&nbsp;&nbsp;&nbsp;Do you know what else I can do?&nbsp;Thanks for all

artemus · Answer

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

v-alq-msft · Answer

Hi,&nbsp;diegolima&nbsp;
&nbsp;
Power query date&nbsp;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&nbsp;thread&nbsp;to see if it helps.
&nbsp;
Best Regards
Allan
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;