Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |