Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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:
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.
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
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
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
in my environment:
try using:
Table.TransformColumns(#"Modificato tipo", {"Date", each Date.FromText(_,"it-IT")})
in
toDate
Might be a locale issue. Try using the bottom option of "using locale" and choose something like pt-br
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |