I have an excel file (not created by me) that contains 999+ Columns in the repeating format as below.
I want the data in the format
I know how to do this manually with concatenations, delimiting and then splitting the columns. Is there a more automated way of me transforming this within Query Editor?
If anyone wants to see the actual data it is in the spreadsheet at http://www.improvementservice.org.uk/documents/benchmarking/1718rawdata.xlsx
Go to Solution.
You could try this way as below:
Selected all the columns except Area column, then unpivot these columns.
Then split Attribute column to Indicator column and Year column.
Now rename the column.
and here is sample pbix file, please try it.
View solution in original post
Kudos to you if you earned one of these! Check your inbox for a notification.
Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.
Find out where you can attend!