Easy way to convert seconds to minutes in multiple columns?

[Anonymous]

I'm creating multiple reports based off of .txt files where the time is in seconds.  I have about 13 columns in a single data source that need to be converted from seconds to minutes.  The kicker is that I have 8 different data sources which brings me to over 100 columns that need to be converted from seconds to minutes.

The text files are coming from Avaya and I don't have an option to change how theyre formatted before they become text files.

I know I can simply do something like this for each column but it's just going to take time and I don't think it would be the best way to do it resource wise.  Any better way to convert these columns?

NewColumn = [col]/60

Currently it looks like this

col1	col2
180	120

Looking to easily/efficiently transform 13 columns to look like this

newcol2	newcol2
3	2

[TomMartens · Accepted solution]

Hey @Anonymous ,

you might consider this using these steps in power query

1. mark the columns that contain the seconds
2. unpivot these columns, this will create two new columns, attribute (the name of the former column) and value
3. create a new column "min" divide the value by 60
4. delete the value column
5. pivot the attribute min columns back again

Maybe this approach will provide some new ideas.

Regards,
Tom

[Anonymous]

Wow this absolutely blows my mind I've never done this before it's amazing!  Thanks so much this makes things SO much easier.

[TomMartens]

My pleasure!