Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a report with time columns that are expressed in seconds from midnight (so for ex. 48308 corresponds to 1:25 p.m.).
As I have a few of those columns, I wanted to create a custom function to be applied to each of those columns to make the query more efficient...
However I am at my first attempts to write custom fucntions and I definitiely I am missing something. So aside from the code which is definitely improvable - I did not spend much time on it - this is what I came up with:
( ColumnToConvert) =>
let
Source=ColumnToConvert,
#"Added Custom" = Table.AddColumn(Source, "Custom", each ([ColumnToConvert])/86400*24),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.Mod([ColumnToConvert],(86400/24))/60),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom.2", each Time.From(Text.BeforeDelimiter(Text.From([Custom], "en-US"), ".") & ":" & Text.From([Custom.1]))),
#"Removed Columns" = Table.RemoveColumns( #"Added Custom2",{ColumnToConvert}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", ColumnToConvert}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Custom", "Custom.1"})
in
#"Removed Columns1"
But this does not work as Source is supposed to be a table called in the second line, not a column name.
Can anybody let me know what I am doing wrong and how to proceed?
Thanks!!!!
Kind regards
Valeria
Solved! Go to Solution.
Is there a Time.FromSeconds function in M? Why not just go
Table.AddColumn(ColumnToConvert, "Time", each Time.From(Value.Divide([ColumnToConvert], 86400)), type time)
No need for the Mod either.
--Nate
Is there a Time.FromSeconds function in M? Why not just go
Table.AddColumn(ColumnToConvert, "Time", each Time.From(Value.Divide([ColumnToConvert], 86400)), type time)
No need for the Mod either.
--Nate
Thank you so much!!!!
So now I see where I was going wrong with my custom function - I had only seen examples of tables as inputs, so I was not sure where to start from. I could not find indeed the fucntion Time.FromSeconds so I ended up with the following as per @watkinnc reply:
my function:
(ColumnToConvert as any) => let Source = ColumnToConvert, Converted = Time.From(Value.Divide(ColumnToConvert, 86400)) in Converted
used in my query:
= Table.TransformColumnTypes(Table.TransformColumns( PreviousStep,{"Confirmed Start Time", each fxConvertToTime(_)}),{"Confirmed Start Time",type time})
So I don't even have to add an extra column to delete it afterwards - much cleaner.
I also did not know that Time.From() could just work so simply on the division without the need for the rest...
Thank you both so much!
Kind regards
Valeria
Based on your description, it seems like you are trying to create a custom function to convert time values expressed in seconds from midnight to a time format. However, the function you provided is incomplete and contains errors.
To fix the error you are encountering, you need to pass the table containing the column you want to convert as a parameter to the function. Here's an updated version of your function that should work:
(ColumnToConvert as any) => let Source = ColumnToConvert, Converted = Time.FromSeconds(Number.Mod(Source, 86400)) in Converted
This function takes a column as a parameter, converts the value in each row to a time format, and returns the converted column.
To apply this function to your table columns, you can use the "Add Custom Column" feature in Power Query. Select the column you want to convert, go to the "Add Column" tab, click on "Custom Column," and enter the formula above.
I hope this helps! Let me know if you have any further questions.