Calculated Table

[BIswajit_Das]

Hello Experts,
Need Guidance with the below structure

Table1 with data :

id	party	value
1	B	10845
2	B	22921
1	I	20921
5	I	5807
7	I	40044
9	B	24634
9	I	11967
14	I	869

The Required Result Table :

id	B	I
1	10845	20921
2	22921
5		5807
7		40044
9	24634	11967
10
14		869

Using Calculated table on Power BI desktop

Thanks & Regards....

[sjoerdvn · Accepted solution]

I don't think it is possible to do a pivot operation dynamically in dax; only in power query. At least I can not think of any way to achieve this.

[sjoerdvn]

Table2 = ADDCOLUMNS(VALUES(Table1[id])
    ,"B", CALCULATE(VALUES(Table1[value]),ALLEXCEPT(Table1,Table1[id]), Table1[party]="B")
    ,"I", CALCULATE(VALUES(Table1[value]),ALLEXCEPT(Table1,Table1[id]), Table1[party]="I")
)

[BIswajit_Das]

Hello @sjoerdvn 
Thanks for responding
Yeah we can do it this way
But we  have mention column names each time
Isn't there any other way to acheive this?

[Anonymous · Accepted solution]

Hi @BIswajit_Das 

sjoerdvn is right.

DAX has a number of limitations, and if this is a calculated table, this is the only way to do it

Or you can try to export the data from the calculated table and import it again to use power query. But it's going to be very complicated.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sjoerdvn · Accepted solution]

I don't think it is possible to do a pivot operation dynamically in dax; only in power query. At least I can not think of any way to achieve this.

[BIswajit_Das]

Hello @Anonymous, @Idrissshatila 

Thanks for responses but as I mentioned above my table is a calculated table which is not gonna show in transform data section
That's why I wonder if there's another way to do so..
Thanks & Regards...

[Anonymous]

Hi @BIswajit_Das 

Please try this:

Click the Transform data in the Home pane:

Copy the origin table and paste it:

      

Select the party column and click the Pivot Column:

The result is as follow:

Finally, Close & Apply.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Idrissshatila]

Hello @BIswajit_Das ,

you can acheive the below in Power Query with pivot.

check it out https://youtu.be/Sm_NodeFvPg?si=cKZpyEYOpOR2FXnH