CODEWITHSUNDEEP
exceldatepowerbipowerquerydate-formatting
obskure_ie
obskure_ie

Reputation: 81

Power BI: Convert text (yyyymmdd) to date (dd/mm/yyyy)

I'm specifically trying to convert string in Column A : yyyymmdd to dd/mm/yyyy date format using Power Query Editor in Power BI. I can already perform this in Excel using the formula below: Any ideas

Excel

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Upvotes: 6

Views: 90332

Answers (6)

Manik Luthra
Manik Luthra

Reputation: 1

If you have a date embedded in text, try the following to extract the date from it. e.g. Capability[Source.Name] = CapabilityExtract_06142023.csv

ExtractDate = Date(MID(Capability[Source.Name],23,4),MID(Capability[Source.Name],19,2), MID(Capability[Source.Name],21,2))

Upvotes: 0

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Solution 1 : Highlight the specific column , and on the Transform tab you can select Detect Data Type.
Solution 2 : Try to create a new column named [dateFormatted] and apply the formula as follows:

dateFormatted = Date(Left([date],4),Right(left([date],6),2),right([date],2))

select the new column and change its type to date as follows:

enter image description here

[dateFormatted] will now be of type date, formatted as: dd Mmm yyyy

Upvotes: 6

Rick de Groot - gorilla.bi
Rick de Groot - gorilla.bi

Reputation: 416

You can make use of the Date.FromText function.

= Date.ToText( Date.FromText( "20221231" ), "dd/MM/yyyy" )

Upvotes: 4

JVS
JVS

Reputation: 21

Using insert column from examples, I converted DDMMYYYY (convert to text) to DD/MM/YYYY (convert to date) which ended up being this:

= Table.AddColumn(#"Changed Type", "OrganisationStartDate", each Text.Combine({Text.Start([organisation_start_date], 2), "/", Text.Middle([organisation_start_date], 2, 2), "/", Text.Middle([organisation_start_date], 4)}), type text)

Upvotes: 2

obskure_ie
obskure_ie

Reputation: 81

Solved: Highlight column in question, and on the Transform tab, select Detect Data Type. This switches it from String to Whole number. Then alter Data Type to Date. format provided is dd/mm/yyyy. Hope someone else finds this useful

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60444

Just set the datatype to date.

M-Code although can be done through the GUI

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}})
in
    #"Changed Type"

Upvotes: 2

Related Questions