Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Srini1053
New Member

Convert Text Data Type to Date?

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?

Srini1053_0-1691474021626.png

 

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

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

Screenshot_2.pngScreenshot_3.png

 

View solution in original post

10 REPLIES 10
Ahmedx
Super User
Super User

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

Screenshot_2.pngScreenshot_3.png

 

Thanks alot @Ahmedx . I got the required solution.

hashtag_pete
Helper V
Helper V

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

Ahmedx
Super User
Super User

Is this what you are looking for?

Screenshot_1.png

hashtag_pete
Helper V
Helper V

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.

Srini1053_0-1691475464769.png

 

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

 

vicky_
Super User
Super User

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.