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 

slorin's avatar
slorin
Icon for Super User rankSuper User
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 

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

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