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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Sharkybu
Frequent Visitor

Comparing multiple rows with the same ID

Hello, New to the bi world.

 

I'm trying to build a report that compares rows with the same ID and  show if each column has the same values  in each row. 

When sometimes there can be more the two rows per ID that only some are the same and null values in some cells.

 

Example (yes, the data can be that messy):

ID columnA columnB ColumnC

1       XXX        AAA        000

1       YYY        AAA         222

2       FFF         BBB         123

2       PPP        TTT          000

2       PPP        BBB          null

 

What I need to show is

ID                      1                 2

columnA          different      same and different

columnB          same           same and different

ColumnC         different      different

 

In cases where ID's have only two rows, I can do It (with pivoting, grouping and transposing). So far I separated the data to two tables and manually checked the ones with more than two rows.

 

Now the data base has more than 1500 IDs and it's going to multiply. 

And currently I have 5 columns but that could change as well.

And I had up to 5 rows for some IDs.

 

Is there any way to do the comparison?

Thank you

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

Hi,

 

= Table.Group(YourSource, {"ID"}, 
{{"columnA", each if List.Count(List.Distinct([columnA]))=1 then "Same"
else if List.Count(List.Distinct([columnA]))=List.Count([columnA]) then "Different"
else "Same and Different" , type text},
{"columnB", each if List.Count(List.Distinct([columnB]))=1 then "Same"
else if List.Count(List.Distinct([columnB]))=List.Count([columnB]) then "Different"
else "Same and Different" , type text},
{"columnC", each if List.Count(List.Distinct([columnC]))=1 then "Same"
else if List.Count(List.Distinct([columnC]))=List.Count([columnC]) then "Different"
else "Same and Different" , type text}})

Stéphane 

View solution in original post

4 REPLIES 4
AlienSx
Super User
Super User

Hello, @Sharkybu are you sure you want to have 1500 IDs as column names? Think twice. 

let
    Source = your_table,
    col_names = List.Buffer(List.RemoveItems(Table.ColumnNames(Source), {"ID"})),
    fx_status = (lst as list) as text =>
        if List.IsDistinct(lst) then "different"
        else if List.Count(List.Distinct(lst)) = 1 then "same"
        else "same and different",
    fx_g = (tbl as table) => 
        [cols = List.Buffer(Table.ToColumns(Table.SelectColumns(tbl, col_names))),
        z = Record.FromList(List.Transform(cols, fx_status), col_names)][z], 
    g = Table.Group(Source, "ID", {"status", (x) => fx_g(x)}),
    expand = Table.ExpandRecordColumn(g, "status", col_names)
in
    expand

Thank you for your help.

And I made a mistake in showing how I want the end result- I mixed the columns and rows.

Thank you

slorin
Super User
Super User

Hi,

 

= Table.Group(YourSource, {"ID"}, 
{{"columnA", each if List.Count(List.Distinct([columnA]))=1 then "Same"
else if List.Count(List.Distinct([columnA]))=List.Count([columnA]) then "Different"
else "Same and Different" , type text},
{"columnB", each if List.Count(List.Distinct([columnB]))=1 then "Same"
else if List.Count(List.Distinct([columnB]))=List.Count([columnB]) then "Different"
else "Same and Different" , type text},
{"columnC", each if List.Count(List.Distinct([columnC]))=1 then "Same"
else if List.Count(List.Distinct([columnC]))=List.Count([columnC]) then "Different"
else "Same and Different" , type text}})

Stéphane 

Thank you, Thank you , Thank you.
That is exactly what I needed.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors