cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Multiple rows per unique ID

I have a table with multiple rows per unique ID, and some columns which reference "flags" (basically a 1 or  0) that something has occurred relative to that ID. Due to the way the data is structured, there can bemultiple instances of a flag showing up for each ID:

ID      ColA ColB ColC
31      1       1        0
31      0      1        0
31      1       1       0
144     1      0       0
144     1     0       0
144     1     0       0
26      0      0       0
26      0      0       1


But I don't care if the "1" shows up multiple times for each ID --- I want to add three columns which basically indicates, for each ID, whether or not a flag has occurred there ever (as indicated by the "1"). Call them ColA_Flag, ColB_Flag, and ColC_Flag. 


So for ID 31, there would be a "yes" for Col A amd Col B, but a "no" for Col C. 

 

So it would look like this:

 

ID      ColA ColB ColC   FlagA   FlagB  FlagC
31      1       1        0         Yes     Yes       No
31      0      1        0           Yes     Yes      No
31      1       1       0          Yes      Yes     No
144     1      0       0          Yes      No      No
144     1     0       0           Yes      No      No
144     1     0       0           Yes      No      No
26      0      0       0          No      No       Yes
26      0      0       1          No      No       Yes


Any help is appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

You can use Min / Max

 

FlagA = If(Max(Table1[ColA]) = 1, "Yes", "No")
 
 
minmaxtable.png

View solution in original post

2 REPLIES 2
Highlighted
Resolver II
Resolver II

You can use Min / Max

 

FlagA = If(Max(Table1[ColA]) = 1, "Yes", "No")
 
 
minmaxtable.png

View solution in original post

Highlighted

Thanks! Worked great!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors