Forum Discussion

Sharkybu's avatar
Sharkybu
Icon for Helper II rankHelper II
2 years ago
Solved

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 ...
Need Help
  • slorin's avatar
    slorin
    2 years ago

    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 

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago

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's avatar
Sharkybu
Icon for Helper II rankHelper II
2 years ago

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