Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Pbiuserr
Post Prodigy
Post Prodigy

Random binary (1 and 0) column for each ID

Hello,

My database looks like that

IDStatus
ID1A
ID1A
ID1A
ID2A
ID2A
ID3A

 

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

IDStatusNewBinaryColumn
ID1A1
ID1A1
ID1A1
ID2A0
ID2A0
ID3A1

 

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

Thank you in advance

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.