Text Format To Date Format

[gmazza76]

Good morning,

I am using PowerPivot to look at a large data file I have been sent but I am having an issue with the dates in a column.

I have a column that holds an amount of data in Column A in including the time but it shows as
"TIME=20201026021813357". I have used "=mid()" to pull the date out of this as "20201026", but I need to change this into an actual date format.

I would look at power Query, but I haven't used this before so is there an easy way to fix this in PowerPivot as I will be linking this file on a weekly basis and would like the calculations to work automatically when the information is uploaded weekly.

I am currently using the below and it gives "2610", but I am having an issue adding the 3rd part in to add the year.

=CONCATENATE(mid(Table[F1],12,2),MID(Table[F1],10,2))

thanks in advance
Gavin

 

[gmazza76]

I have used the following to amend the date as follows

=date(mid(Table[F1],12,2),Mid(Table[F1],10,2),mid(Table[F1],6,4))

using the Table[F1] has the value "TIME=20201026021813357", but the date is coming out as "11/07/1932"

Any suggestions?

 

[sandy666]

maybe

Column1	First Characters
20201026021813357	26/10/2020

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    First10 = Table.AddColumn(Source, "First Characters", each Text.Start([Column1], 8), type text),
    Type = Table.TransformColumnTypes(First10,{{"First Characters", type date}})
in
    Type

 

[gmazza76]

thanks for this @sandy666, but I haven't used Power Query in PowerPivot at all.
I am unsure if the works systems have this so is there anyway I can do this without using PowerQuery

 

[sandy666]

Use Power Query , do any transformation what you want, eg. text string to date then load it to the Data Model for Power Pivot

 

[gmazza76]

thank you @sandy666, I have managed to get the original formula working as I had tried to split the date incorrectly. ie year should have been the first part where as I did it last.

I will be trying the other Power Query Option as it seems more straight forward

 

[sandy666]

year should have been the first part where as I did it last

it depends on the system date, ie. yours can be yyyy/mm/dd (probably) , but mine is dd/mm/yyy
anyway function is : (PQ) #date(yyyy, mm, dd) or (DAX) DATE(<year>, <month>, <day>)
glad you solved that problem
have a nice day