Unpivot calculated columns using DAX query

[arajan111]

Hi, I have the following columns

User	Critical Use 1	Critical Use 2	Critical USe 3	Critical Use	Critical Use Calc	Creative 1	Creative 2	Creative_Calc	Creative
ABC	40	40	30	33.6	Adopting	40	60	50	Adopting
DEF	40	60	40	46.67	Exploring	60	60	60	Performing
GHI	40	20	20	26.67	Exploring	20	60	40	Adopting

The columns Critical Use 1, Critical Use 2, Critical USe 3, Critical Use Calc, Critical Use, Creative 1, Creative 2, Creative_Calc, Creative are all calculated columns.

How can I get  a resultant tableto pivot the data above?

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

[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?

[arajan111]

@ryan_mayu Because these columns are calculated columns, they do not appear in Query editor

[ryan_mayu]

could you pls provide some sample data?

[arajan111]

Please see example below:

Response 1, 2, 3, 4 and 5 are original columns. The remaining columns are calculated columns. I would like to pivot the data using the calculated columns to obtaing the resultant 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

40

30

33.6

Adopting

40

60

50

Adopting

DEF

Adopting

Leading

Performing

Adopting

Performing

40

60

40

46.67

Exploring

60

60

60

Performing

GHI

Performing

Experienced

Leading

Performing

Adopting

40

20

20

26.67

Exploring

20

60

40

Adopting

[ryan_mayu]

how you get the critial user 1 to the end? i think based on the first 5 columns, we can't get that output. I think we need to transpose and create measures instead of the calculated columns.

[ryan_mayu]

@arajan111 

you can select the first column and unpivot other columns in PQ