Forum Discussion
Using a function to tranform similar data tables
Hi there,
I'm looking for some data transformation help. I am importing a PDF into power query which has around 50 tables in it. These 50 tables are set out in 8 different formats.
I am looking to clean each table so they are set out identically and can be joined together at a later point.
So far I have copied the M code from the advanced editor, pasted onto another table, and made minor changes - this works well for if the transformation between tables requires exactly the same steps (ie they have the same table format to begin with)
When I move onto a table with a new layout, I am starting all of the changes again in powerquery, and then copying this across.
However, whilst the different layouts are significantly different, there are some fixed things that I do to each of the 50 tables (removing set columns, adding in calculated columns)
For these discrete bits is it possible to set up a function to do several applied steps at once? - so when I'm amending one of the 8 different table formats I can just run it to do the generic changes? I'm aware if I was better at M I could just copy the specific lines of code, and that might be the best solution, though I thought I would check with the community if a function can be set up to solve the problem.
That is probably a very basic way of describing something very common in Power Query, if anyone can point me in the direction of a thread or resource that might help it would be appreciated. The youtube videos I've watched to date don't quite cover what I'm after.
Thanks
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.
4 Replies
PhilipTreacy Thank you, the time transformation was the thing taking the most steps, which with your code as removed all the unecessary ones, so it looks like at present I don't need a function.
Regards
TomHi tomshaw83
Yes this could be done but without knowing the exact steps you are talking about it's difficult to say how to implement it. Can you post some sample PDF's and querys indicating the steps you want to turn into a function?
Cheers
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
TomHi 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.
