Transform unique values in a column as new columns

[Anonymous]

Hi Community

I hope you can help with the issue I have been sttruggling with

I am trying to extract the unique values from a column in a table and include them as new columns in the same table (I have been able to do this), and I need that correspondant value based on the Record ID to be tranformed in the respective column as 1 or 0. I do not want to do this task as "Conditional Column" because there could be more than 1,000 unique values, so it will take some time to do it this way.

Basically, what I need is from a table as per below

Transform into a table as per below

I hope this makes sense and you can help me with it

Many Thanks

[Avantika-Thakur · Accepted solution]

Hi @Anonymous ,

You can try with the following steps in Power Query editor :

1) First you will have to create a column in your table which holds value "1" for all the rows

2) Click on the Type column and select Pivot Column option from Transform ribbon. Select the "Value" column in "Values Column" section and Count(All) under "Aggregate Value Function"

3) You will get the required result as below :

Hope this answers your query!

Please accept the solution if this answers your question.

Thanks,

Avantika Thakur

[Anonymous]

Hi Avantika

Thanks for that, it works beatifully. Just on final question. Is it possible not to include the prefix "1.2"?

Regards,

Carlos

[Avantika-Thakur]

Hi @Anonymous ,

"1.2" is just the datatype of the column which is currently set to decimal number and you can change the data type to any other format as per requirement by clicking on the "1.2" on every column.

Thanks,

Avantika

[Anonymous]

Ops, my bad

Forgot my comment

Many Thanks for the quick reply

Regards,

Carlos