Comparing multiple rows with the same ID

[Sharkybu]

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

[AlienSx]

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

[Sharkybu]

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 · Accepted solution]

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 

[Sharkybu]

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