Forum Discussion

pmazara's avatar
pmazara
Frequent Visitor
6 years ago
Solved

Conditional Replace Value across table (multiple rows/columns) for values greater than 1

Seeking help in figuring out a query that will replace all values greater than 1 with 1   see attached screenshot
Need Help
Show and Tell
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try to add custom columns in Query Editor:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

amitchandak's avatar
amitchandak
Super User
6 years ago

Create new columns

Category 1 new =if(table[Category 1 ]>1,1,0)

Category 2 new =if(table[Category 2 ]>1,1,0)

Category 3 new =if(table[Category 3 ]>1,1,0)

pmazara's avatar
pmazara
Frequent Visitor
6 years ago

amitchandak - not practicable as columns can vary (15-25) from source data. currently has 15 columns. I'd rather transform the the data in edit query within existing columns

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You can do while doing some calculation like

     

    New Measure = Sumx(table,if(table[category]>1,1,0))