Search code examples
excelpowerbipowerquerypowerbi-desktopm

List.Distinct and List.Count Challenge

Within PQ, I have a table of data (below) to which I am trying to determine whether all the columns titled Plan Status-# are the same, excluding blanks, and if all the same, display that value and if not display "Varies across plans"

PQ Data

The PQ code is below where I use List.Distinct to create a list of all "unique values". I then use List.Count to count this number in the list and if 1, set the column equal to the Distinct value.

If List.Count({List.Distinct({[#"Plan Status-H"],[#"Plan Status-D"],[#"Plan Status-S"],[#"Plan Status-M"],[#"Plan Status-C"],[#"Plan Status-U"]})})=1 then List.Distinct({[#"Plan Status-H"],[#"Plan Status-D"],[#"Plan Status-S"],[#"Plan Status-M"],[#"Plan Status-C"],[#"Plan Status-U"]}) else "Varies across plans"

As per the table above, the List.Count does not seem to working correctly as some of the records show a merged value of items in the list which means the List.Count for a list with multiple values is calcing as 1.

Solution

  • You have extra brackets in your code. Try this:

    if List.Count(List.Distinct({[#"Plan Status-H"],[#"Plan Status-D"],[#"Plan Status-S"],[#"Plan Status-M"],[#"Plan Status-C"],[#"Plan Status-U"]}))=1 
then List.Distinct({[#"Plan Status-H"],[#"Plan Status-D"],[#"Plan Status-S"],[#"Plan Status-M"],[#"Plan Status-C"],[#"Plan Status-U"]}) 
else "Varies across plans"