Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Extract date and time from google analytics table

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?

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

     

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.