Pivot columns

[adavid999]

Hello,

I would like to pivot a column in my query that will go from this:

ID	Choice
1	A
2	A
3	A
3	B
4	A
5	A
5	B

to this:

ID	Choice1	Choice2
1	A
2	A
3	A	B
4	A
5	A	B

When I try to do this I receive an error message. I am very new to this so hopefully something simple I am not doing?

Many thanks,

A

[v-kelly-msft]

Hi @adavid999 ,

Go to edit queries >click on the column"Choice">"Add Column">"Conditional Column">then create 2 columns as below:

And you will see:

Then choose  close and apply :

Finally, you need to create a new table using a dax expression as below:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[ID],
    "Choice 1", MAX ( 'Table'[Custom] ),
    "Choice 2", MAX ( 'Table'[Custom.1] )
)

And you will see :

For the related .pbix file,pls click here.

Best Regards,
Kelly

[adavid999]

Dear @v-kelly-msft ,

Thank you this worked! (and if you have time/patience) if there are other exisitng fields I would like to bring into the new table, linked to choices, how would I do this? So this:

ID	Choice	Colour
1	A	Red
2	A	Green
3	A	Red
3	B	Green
4	A	Red
5	B	Green
5	A	Red

Would become

ID	Choice1	Choice2	Colour1	Colour2
1	A
2	A
3	A	B	Red	Green
4	A		Red
5	B	A	Green	Red

In effect I guess I am asking how to transpose rows to columns within a table, while maintining link to unique id. I'm not sure if I have explained it clearly!

Many thanks,

Adam