Transpose and pivot data for calculated columns

[arajan111]

The below is the original data:

User	Response 1	Response 2	Response 3	Response 4	Response 5
ABC	Adopting	Performing	Adopting	Leading	Experienced
DEF	Adopting	Leading	Performing	Adopting	Performing
GHI	Performing	Experienced	Leading	Performing	Adopting

I have added some calculated columns to the above table:

User

Response 1

Response 2

Response 3

Response 4

Response 5

Critical Use 1

Critical Use 2

Critical USe 3

Critical Use

Critical Use Calc

Creative 1

Creative 2

Creative_Calc

Creative

ABC

Adopting

Performing

Adopting

Leading

Experienced

40

30

40

36.67

Adopting

60

80

70

Adopting

DEF

Adopting

Leading

Performing

Adopting

Performing

40

60

30

43.33

Exploring

40

30

35

Performing

GHI

Performing

Experienced

Leading

Performing

Adopting

30

80

60

56.67

Exploring

30

80

55

Adopting

These calculations are based on the below lookup tables:

Adopting	40
Leading	60
Performing	30
Experienced	80

Adopting	36.67
Exploring	43.37
Exploring	56.67
Adopting	55
Adopting	70
Performing	35

I now need to get the table to pivot the data as follows:

ABC	Critical Use 1	40
ABC	Critical Use 2	40
ABC	Critical USe 3	30
ABC	Critical Use	33.6
ABC	Critical Use Calc	Adopting
ABC	Creative 1	40
ABC	Creative 2	60
ABC	Creative_Calc	50
ABC	Creative	Adopting

I cannot use the transform function in Query editor because the calculated columns wouldnt appear there. What would be an alternative way to get this displayed?

[DataNinja777 · Accepted solution]

Hi @arajan111 

In this case, it is better to use Power Query to merge data in order to bring numerical values in your table, and unpivot the wide table after merging the additional numerical columns.  Transforming your data to a long narrow table (the type of table which dax can efficienty process and consume), is Power Query's forte.  

Best regards,