Extract date and time from google analytics table

[Anonymous]

I import Table with Data time from Google Analytics in a format like this:

When I try to extract with a split button: year, month, day, hour, the moment I get errors. Because after extracting 4 numbers of a year, the first zero is deleted

Then I get a different number of numbers that I do not know how to deal with to get a date and time separately...
Do you have any idea how to extract data from such a format?

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may apply the following steps in Power Query. Here are m codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjA0MrIwMjQ0UIrVQQgYAkVgAoYGhgamRobGZsgChkB1FkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Hour and Minute" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Hour and Minute", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Number.From( Text.Start([Date Hour and Minute],4))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each Number.From( Text.Middle([Date Hour and Minute],4,2))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Number.From( Text.Middle([Date Hour and Minute],6,2))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Hour", each Number.From( Text.Middle([Date Hour and Minute],8,2))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Minute", each Number.From( Text.End([Date Hour and Minute],2)))
in
    #"Added Custom4"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,

try like Date.FromText(Text.Start([datehourtime],8))

Date.FromText(Text.Start([datehourtime] & "" ,8))

Or

Date.FromText(Text.Start([datehourtime] ,2) & " " & Text.Middle([datehourtime] ,4,2)& " " & Text.Middle([datehourtime] ,6,2))