write a custom function to transform a column

[ValeriaBreve]

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

[Anonymous · Accepted 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

[ValeriaBreve]

@Anonymous , @Adamboer 

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 @Anonymous 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

[Adamboer]

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.