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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors