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 have a fact table with a 24h time column 00:23:03, 13:56:45 etc. I would like to create a new column without seconds and where minutes is rounded to 00, 15, 30 or 45.
First 14 minutes goes to 13:00, minutes 15 -29 goes to 13:15, minutes 30-44 goes to 13:30, 45-59 goes to 13:45
Output to NEW text column like 1315, 0030 or 13:30
Suggestions in M query (preferabale) or as calculated column
Thanks
Solved! Go to Solution.
I was able to do it by creating a calculated column as I'm not an expert in M query
Try the following
Change time column format to text instead of date/time. Then create a calculated column:
Column = LEFT(Sheet1[Time],2)&":"&
SWITCH(TRUE(),
VALUE(MID(Sheet1[Time],4,2))<15,"00",
AND(VALUE(MID(Sheet1[Time],4,2))>=15,VALUE(MID(Sheet1[Time],4,2))<30),"15",
AND(VALUE(MID(Sheet1[Time],4,2))>=30,VALUE(MID(Sheet1[Time],4,2))<45),"30",
"45")
Calculated Column:
It work fine with all 890 000 rows
But I had to change to text column inside Query. It didnt work to just change in Desktop
Thanks all and now I will try inside M Query.
One question though, peformance wise I knwo the differenxe between Measure (=CPU) and Calc. column (RAM)
But doing a new column in PBI M Query compared to a calculated column?
Hi
I think you can do this in power quary.
Please refer my below query. (Hope you can understand this) else tell I'll explain it for you.
let Source = Excel.Workbook(File.Contents("C:\Users\Dilumd\OneDrive - \PowerBi\Help\time.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"#", type text}, {"Time", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Time", "Time - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Time - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time - Copy.1", "Time - Copy.2", "Time - Copy.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time - Copy.1", type text}, {"Time - Copy.2", Int64.Type}, {"Time - Copy.3", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Min_Rounding", each if [#"Time - Copy.2"] <= 15 then "00" else if [#"Time - Copy.2"] <= 30 then "15" else if [#"Time - Copy.2"] <= 45 then "30" else if [#"Time - Copy.2"] <= 59 then "45" else "00" ), #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Min_Rounding", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type2", "Time_New", each [#"Time - Copy.1"]&":"&[Min_Rounding]), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Time_New", type text}}) in #"Changed Type3"
Hi, didnt work well for me. Here is my script. See screenshot as well. My Problem is also to add a ZERO to the singel digit value ´to the hours from 0-9 but maybe your solution is handling that?
let
Source = Sql.Databases("XXXX01"),
XXXX_RPT = Source{[Name="SLLIT_RPT"]}[Data],
rpt_vCallData = XXXX_RPT{[Schema="rpt",Item="vCallData"]}[Data],
#"Duplicated Column" = Table.DuplicateColumn(rpt_vCallData, "CreatedTime", "CreatedTime - Copy"),
#"Calculated Start of Hour" = Table.TransformColumns(#"Duplicated Column",{{"CreatedTime - Copy", Time.StartOfHour, type time}}),
#"Extracted Hour" = Table.TransformColumns(#"Calculated Start of Hour",{{"CreatedTime - Copy", Time.Hour}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Hour",{{"CreatedTime - Copy", "Hour"}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "CreatedTime", "CreatedTime - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column1",{{"CreatedTime - Copy", "Timmar"}}),
#"Calculated Start of Hour1" = Table.TransformColumns(#"Renamed Columns1",{{"Timmar", Time.StartOfHour, type time}}),
#"Changed Type" = Table.TransformColumnTypes(#"Calculated Start of Hour1",{{"Timmar", type time}, {"CreatedTime", type text}}),
#"Duplicated Column2" = Table.DuplicateColumn(#"Changed Type", "CreatedTime", "CreatedTime - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column2",{{"CreatedTime - Copy", "INTTime"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns2", "INTTime", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"INTTime.1", "INTTime.2", "INTTime.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"INTTime.1", Int64.Type}, {"INTTime.2", Int64.Type}, {"INTTime.3", Currency.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"INTTime.3"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [INTTime.2] < 15 then "00" else if [INTTime.2] < 30 then "15" else if [INTTime.2] < 45 then "30" else if [INTTime.2] <= 59 then "45" else "00" ),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom.1", each [INTTime.1]&[Custom])
in
#"Added Custom"
Maybe try my solution?
Or just:
let Source = #table(type table[Time = time],{{#time(3,14,59)},{#time(13,15,00)},{#time(13,46,00)}}), #"Added Custom" = Table.AddColumn(Source, "TimeText", each 100*Time.Hour([Time])+15*Number.IntegerDivide(Time.Minute([Time]),15)), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"TimeText", type text}}), LeadingZeroes = Table.TransformColumns(#"Changed Type",{{"TimeText", each Text.PadStart(_,4,"0")}}) in LeadingZeroes
I was able to do it by creating a calculated column as I'm not an expert in M query
Try the following
Change time column format to text instead of date/time. Then create a calculated column:
Column = LEFT(Sheet1[Time],2)&":"&
SWITCH(TRUE(),
VALUE(MID(Sheet1[Time],4,2))<15,"00",
AND(VALUE(MID(Sheet1[Time],4,2))>=15,VALUE(MID(Sheet1[Time],4,2))<30),"15",
AND(VALUE(MID(Sheet1[Time],4,2))>=30,VALUE(MID(Sheet1[Time],4,2))<45),"30",
"45")
Calculated Column:
It work fine with all 890 000 rows
But I had to change to text column inside Query. It didnt work to just change in Desktop
Thanks all and now I will try inside M Query.
One question though, peformance wise I knwo the differenxe between Measure (=CPU) and Calc. column (RAM)
But doing a new column in PBI M Query compared to a calculated column?
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.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |