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

Question

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))

Amira Bedhiafi · Answer 1 · 2022-09-21T09:03:56.803

6

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:

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

edited Sep 21 '22 at 09:03

answered Aug 07 '19 at 10:25

Amira Bedhiafi

8,088
6
24
60

Solution 2 requires an extra ) at the end. – rohrl77 Sep 21 '22 at 08:42
For Solution 2 (and, for users who might ONLY want the date kind output without h:m:s precision) -- During creation of the new column if it happens to be of `Date/Time` type then you may switch it to `Date` type. Otherwise, each new entry would contain a redundant `h:m:s` precision info in form of 00:00:00 values. – Rishabh Barman Mar 25 '23 at 07:52

score 2 · Answer 2 · edited Aug 14 '19 at 15:32

2

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

edited Aug 14 '19 at 15:32

jps

20,041
15
75
79

answered Aug 07 '19 at 10:24

obskure_ie

71
1
1
5

You don't need to do the Detect Data Type step. Just select the icon on the left side of the column heading and choose Date. – TheRizza Jan 07 '22 at 15:33

score 2 · Answer 3 · answered Aug 07 '19 at 11:32

2

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"

answered Aug 07 '19 at 11:32

Ron Rosenfeld

53,870
7
28
60

score 2 · Answer 4 · edited Nov 18 '20 at 09:10

2

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)

edited Nov 18 '20 at 09:10

shuberman

1,416
6
21
38

answered Nov 17 '20 at 04:08

JVS

21
1

score 2 · Answer 5 · edited Apr 08 '22 at 23:02

2

You can make use of the Date.FromText function.

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

edited Apr 08 '22 at 23:02

DharmanBot

1,066
2
6
10

answered Apr 07 '22 at 08:06

Rick de Groot - gorilla.bi

196
2

score 0 · Answer 6 · answered Jun 14 '23 at 12:36

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))

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

6 Answers6