Forum Discussion
Remove seconds from time in Power Query Editor
I punted on trying to change the format of the time column.
I changed my time dimension table to use seconds as the smallest grain. Doing that and then relating it to the time column in my source table did the trick.
This is where I found code for a time dimension table:
https://ginameronek.com/2014/10/01/its-just-a-matter-of-time-power-bi-date-time-dimension-toolkit/
I changed
Source = List.Times(#time(0, 0, 0),MinuteCount, #duration(0,0,1,0)),
to
Source = List.Times(#time(0, 0, 0),86400, #duration(0,0,0,1)),
to get a seconds grain.
Works fine, no noticible lag in performance.
I have tried something similar before. The issue is that the data coming from Salesforce is in this format:
| 7/26/2018 8:20:17 PM |
I can successfully split that column into date and time, with the time in the format h:mm:ss AM/PM
The time table I have is in the format hh:mm. So 13:45 is a value.
The code for the time table starts like this:
GENERATESERIES ( 1, 1440, 1 )
, "TimeValue", TIME ( 0, [Value], 0 )
I don't want to create the time table at the seconds grain because I don't need that accuracy, and I don't want that big a table. So I'm trying to transform 8:20:17 PM into 20:17, but still a time type.
How about this?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMjawMjFVitUBcgytTEysjI2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type time}}),
#"Duplicated Column" = Table.AddColumn(#"Changed Type", "Column1 - Copy", each Text.From([Column1], "en-US"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"Column1 - Copy"}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns", "Column1", "Column1 - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column1", {{"Column1 - Copy", type text}}, "en-US"), "Column1 - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", Int64.Type}, {"Column1 - Copy.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1 - Copy.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1 - Copy.2.1", "Column1 - Copy.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1 - Copy.2.1", Int64.Type}, {"Column1 - Copy.2.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Column1 - Copy.2.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"Column1 - Copy.1", type text}, {"Column1 - Copy.2.1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each [#"Column1 - Copy.1"] & ":" & [#"Column1 - Copy.2.1"])
in
#"Added Custom"Sorry, it's this one that you want, accounts for PM
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMjawMjFVitUBcgytTEysjI3BHCMDKyAyNFeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type time}}), #"Duplicated Column" = Table.AddColumn(#"Changed Type", "Column1 - Copy", each Text.From([Column1], "en-US"), type text), #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"Column1 - Copy"}), #"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns", "Column1", "Column1 - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column1", {{"Column1 - Copy", type text}}, "en-US"), "Column1 - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", Int64.Type}, {"Column1 - Copy.2", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1 - Copy.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1 - Copy.2.1", "Column1 - Copy.2.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1 - Copy.2.1", Int64.Type}, {"Column1 - Copy.2.2", type text}}), #"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"Column1 - Copy.1", type text}, {"Column1 - Copy.2.1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each if [#"Column1 - Copy.2.2"] = "AM" then 1 else 2), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "NewHour", each if [#"Column1 - Copy.2.2"] = "AM" then [#"Column1 - Copy.1"] else Number.FromText([#"Column1 - Copy.1"]) + 12), #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom1",{{"NewHour", type text}}), #"Added Custom2" = Table.AddColumn(#"Changed Type4", "NewTime", each [NewHour] & ":" & [#"Column1 - Copy.2.1"]), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Column1 - Copy.1", "Column1 - Copy.2.1", "Column1 - Copy.2.2", "NewHour"}) in #"Removed Columns2"I'm doint this in Power BI, and I'm afraid I don't know how to run that code. I tried something like that where I split the column into hours and minutes, but when I merged them it didn't add the zeros to the minutes, leaving this:
2:05 --> 2:5
Does your code deal with this? And how can I run it from Power BI?
I'll have to look at the single digit issue. To run the code, open up Power Query Editor. Create a new blank query. While in that query, select Advanced Editor from the ribbon. Use Ctrl+A to select all of the existing code and paste in the code that I have replacing all of the existing code. Close out of the Advanced Editor.