Can somebody help me to organize below Unstructured data

[Anonymous]

Below is the table I have:

Question : I have to create a Dynamic Clustered column chart with Year on X-Axis and
and dynamically changing measures like AvgAge and MaxSalary on Y - Axis based on slicer selection.

Can anyone 

[KT_Bsmart2gethe · Accepted solution]

Hi @Anonymous ,

You have multi headers data structure. You can follow the steps below:

1. Ensure the columns are Column1,2,3 ... then apply the transpose function (Transform > Transpose)

2. use the fill down function (Transform > Fill > Fill Down) to the column contains the year (i.e. 2016, 2017)

3. Use the merge column function (Transform > Merge Column) to merge the two headers (I,e. Age ... 2016) with no delimiter

4. Transpose the table

5. Promote the header

6. Unpivot the "Salary" and "Incentive" Columns (You will get "Attibute" and "Value" columns)

7. Use the split Column function to split the "Attibute" column by number of character (4 - the number of character for year).

8 Rename the columns.

For the average and max, you can use the DAX to handle it.

Regards

KT 

[Anonymous]

Hi @KT_Bsmart2gethe ,

Thank you so very much for this awesome solution I tried it out and it worked amazingly step by step as you explained.