Transform 999+ column data into something sensible

Question

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 Answer

hi&nbsp;Dr0idy&nbsp;
You could try this way as below:
Step1:
Selected all the columns except Area column, then unpivot these columns.
Step2:
Then split&nbsp;Attribute column to&nbsp;Indicator column and Year column.
Step3:
Now rename the column.
Result:

&nbsp;
and here is sample pbix file, please try it.
&nbsp;
&nbsp;

Forum Discussion

Transform 999+ column data into something sensible

1 Reply

Related content

Table Transformation

Transform columns / type

Data transform using power query

Table Transformation

M Language Transformations