Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ValeriaBreve
Post Patron
Post Patron

write a custom function to transform a column

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

 

 

1 ACCEPTED SOLUTION
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

3 REPLIES 3
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

@watkinnc , @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 @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

 

Adamboer
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors