Random binary (1 and 0) column for each ID

[Pbiuserr]

Hello,

My database looks like that

ID	Status
ID1	A
ID1	A
ID1	A
ID2	A
ID2	A
ID3	A

I would like to just create a random 0 1 column for each ID (so if ID got 1, it needs to have 1 in every row, same with 0).

Expected result

ID	Status	NewBinaryColumn
ID1	A	1
ID1	A	1
ID1	A	1
ID2	A	0
ID2	A	0
ID3	A	1

I have like 500k of distinct ID's. How to get such column?

Thank you in advance

[johnt75 · Accepted solution]

You could do this in Power Query. Make a second query which references your original query, remove all columns other than the ID and then remove duplicate values. Add a new column which is 

Number.Round( Number.RandomBetween(0,1) )

and then join this table back onto the original table