Transform 999+ column data into something sensible

[Dr0idy]

I have an excel file (not created by me) that contains 999+ Columns in the repeating format as below.

Area

Indicator1 Year1

Indicator1 Year2

Indicator1 Year3

Indicator2 Year1

Indicator2Year2

Indicator2 Year3

I want the data in the format

Area

Indicator

Year

Value

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

[v-lili6-msft · Accepted solution]

hi @Dr0idy 

You could try this way as below:

Step1:

Selected all the columns except Area column, then unpivot these columns.

Step2:

Then split Attribute column to Indicator column and Year column.

Step3:

Now rename the column.

Result:

and here is sample pbix file, please try it.