Forum Discussion

JayTG123's avatar
JayTG123
Advocate I
4 years ago
Solved

Replace Unique Text Values to Number 1

Hi all,

 

I am hoping someone can help with this. I have a list of unique text values (Linked Permit column) and just want to replace this with a 1 so it can become a SUM and as a total on a card visual. For everything that is blank I will replace this with a 0 but I would like to know how I can replace all of these unique values as a number 1 please.

Many thanks

Jay

 

Need Help
  • aj1973's avatar
    aj1973
    4 years ago

    Hi JayTG123 

    In Power Query add an conditional column and use IF is equal Blank then return 0, else 1 

     

4 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    4 years ago

    Hi JayTG123 

    In Power Query add an conditional column and use IF is equal Blank then return 0, else 1 

     

    • JayTG123's avatar
      JayTG123
      Advocate I
      4 years ago

      aj1973 thank you so much!! You learn something every day!

      Happy New Year to you ! 

      Jay

      • aj1973's avatar
        aj1973
        Community Champion
        4 years ago

        lol indeed, never stop learning 🙂

         

        Happy new year to you as well

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    BTW, you don't need another column to calculate the sum of non-blank values.

     

    An alternative would be to write a measure that counts the non-blank values.

    CountNonblank = CALCULATE ( COUNT ( Table1[Linked Permit] ), Table1[Linked Permit] <> "" )