Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I am newbie here. I am trying to convert text data type to date, but i am getting an error.
Could you please help me on this?
Solved! Go to Solution.
plse try this
try
Date.FromText("01/"&[Date], [Format="dd/M/yy", Culture="en-EN"]) otherwise
try Date.FromText("01/"&[Date], [Format="dd/MM/yy", Culture="en-EN"]) otherwise Date.From(DateTime.LocalNow())
or
try
Date.FromText("01/"&[Date], [Format="dd/M/yy", Culture="en-EN"]) otherwise
try Date.FromText("01/"&[Date], [Format="dd/MM/yy", Culture="en-EN"]) otherwise null
plse try this
try
Date.FromText("01/"&[Date], [Format="dd/M/yy", Culture="en-EN"]) otherwise
try Date.FromText("01/"&[Date], [Format="dd/MM/yy", Culture="en-EN"]) otherwise Date.From(DateTime.LocalNow())
or
try
Date.FromText("01/"&[Date], [Format="dd/M/yy", Culture="en-EN"]) otherwise
try Date.FromText("01/"&[Date], [Format="dd/MM/yy", Culture="en-EN"]) otherwise null
Hello @Srini1053
then you need to to a bit of conversion first. Add a 1 for the first of the month and change / to .
The code would be the following, you just have to adapt the source
let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> "YTD")),
#"Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each let splitDate = Splitter.SplitTextByDelimiter("/", QuoteStyle.None)([Date]) in Text.Combine({"1.", Text.Combine(splitDate, ".")}), type text),
#"Parsed Date" = Table.AddColumn(#"Custom", "Parsed Date", each Date.From(DateTimeZone.From([Custom])), type date)
in
#"Parsed Date"
hope it helps - if so, kudos and mark as solution are appreciated.
Hashtag_pete
Is this what you are looking for?
Hello @Srini1053
you could filter on <> "YTD", so you get rid of all text. Then try again and it should work.
BR
hashtag_pete
I tried filtered out YTD, but still getting an error.
Hi @Srini1053
You must do first the step of filtering YTD and then change to date format. In the steps on the right, check the order
No it's not working.
Hi @Srini1053
Ok, the problem is that m/yy is not recognized by PowerBI. Maybe you can do this transformation:
let
Origen = Excel.Workbook(File.Contents("C:\Users\Desktop\ex.xlsx"), null, true),
Hoja1_Sheet = Origen{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"Date", type text}}),
#"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([Date] <> "YTD")),
#"Agregar columna personalizada" = Table.AddColumn(#"Filas filtradas", "Personalizado", each Text.Combine({"0", Text.Start([Date], 3), "0", Text.Middle([Date], 2)}), type text),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Agregar columna personalizada",{{"Personalizado", "New Date"}}),
#"Columnas quitadas" = Table.RemoveColumns(#"Columnas con nombre cambiado",{"Date"}),
#"Columnas con nombre cambiado1" = Table.RenameColumns(#"Columnas quitadas",{{"New Date", "Date"}})
in
#"Columnas con nombre cambiado1"
Just in case you don't understand this code... Once you are in the step of <>TD, add a customized column with this code:
Text.Combine({"0", Text.Start([Date], 3), "0", Text.Middle([Date], 2)})
And then simply remove the other column and keep the new one. You will be able to convert to date now
Have a read of https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#datetime-types
your problem is that you have "YTD" which can't be converted into a ddate. A valid date format might look like 01/01/2023.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
104 | |
87 | |
35 | |
35 |
User | Count |
---|---|
152 | |
98 | |
81 | |
61 | |
55 |