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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SamWhite
Frequent Visitor

Find same or different values

I have device details like below , some same device is assigned to different people. I want to create a new column which says if the device is attached to same or different people using power query, please suggest;

SamWhite_0-1691600902238.png

Final result;

SamWhite_1-1691600970887.png

there could be more than two entries for the same device.

1 REPLY 1
ronrsnfld
Super User
Super User

Assuming each DeviceName/AssignedTo row is not duplicated, then

  • Group By DeviceName
  • Aggregate by: ALL
  • Aggregate by: if grouped table has only one row, then "Same" else "Different"
  • Expand the table column only for AssignedTo
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kksKMkvMFTSUQpILUktUorVQRbzSsxNLUYSMwKJleZkpiKJGQPFHPPyMMTccvKLQIKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DeviceName = _t, AssignedTo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DeviceName", type text}, {"AssignedTo", type text}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DeviceName"}, {
        {"All", each _, type table [DeviceName=nullable text, AssignedTo=nullable text]},
        {"Remarks", each if Table.RowCount(_) = 1 then "Same" else "Different", type text}
        }),
    
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"AssignedTo"})
in
    #"Expanded All"

 

ronrsnfld_0-1691602620750.png

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors