Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi, @Galat198
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.
Hi, @Galat198
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.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.