Forum Discussion
Using a function to tranform similar data tables
Hi Tom,
You haven't provided any of your queries so I hope I'm correctly understanding what you are trying to, it sounds like one of the things you are creating is a column with the total seconds from the times in the Time column?
If so you can create a custom column and use this formula to convert times that have a minute component.
= Table.AddColumn(PreviousStepname, "TotalSeconds", each Duration.TotalSeconds(Duration.FromText(Text.Insert([Time],0,"00:"))))To convert times without a minute component use this
= Table.AddColumn(PreviousStepName, "TotalSeconds", each Duration.TotalSeconds(Duration.FromText(Text.Insert([Time],0,"00:00:"))))So for this there isn't any need to write your own function(s). If you can supply your queries and explain the other things you are trying to do that you think a function might be needed, I can check to see if a custom function would make things easier, or if there's a way to achieve the result similar to above.
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Hi PhilipTreacy
This is the PDF that I have been using
https://isl.global/wp-content/uploads/2020/10/results_book_match_1_v2-1.pdf
To give an example, where a function may be useful. I am converting the 'Time' column into 'TimeSeconds' and 'TimeMinutes' (hh:mm.ss.00). For this I am adding a calculated column where I divide any times below 60 seconds by 86400 to create the 'TimeMinutes' and renaming the existing 'Time' column as 'TimeSeconds'.
If the time is already in minutes I am stripping all the numbers out (as PowerQuery doesn't recognise it as a duration in its current form) and then merging them together back in hh:mm.ss.00 format. I then add a calculated column where I multiply the result by 86400 to create TimeSeconds.
I thought particularly the second example may work as a function, as there are a fair few steps involved. So that if I am transforming any table with a time in minutes I can just run it and it spits out the 2 completed columns.
It might be that a simpler data set can show the creation of functions better (or resources already exist on this) that I can then go through and apply here.
Thanks for your time
Tom
Hi Tom,
You haven't provided any of your queries so I hope I'm correctly understanding what you are trying to, it sounds like one of the things you are creating is a column with the total seconds from the times in the Time column?
If so you can create a custom column and use this formula to convert times that have a minute component.
= Table.AddColumn(PreviousStepname, "TotalSeconds", each Duration.TotalSeconds(Duration.FromText(Text.Insert([Time],0,"00:"))))To convert times without a minute component use this
= Table.AddColumn(PreviousStepName, "TotalSeconds", each Duration.TotalSeconds(Duration.FromText(Text.Insert([Time],0,"00:00:"))))So for this there isn't any need to write your own function(s). If you can supply your queries and explain the other things you are trying to do that you think a function might be needed, I can check to see if a custom function would make things easier, or if there's a way to achieve the result similar to above.
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.