Forum Discussion

bolabuga's avatar
bolabuga
Icon for Helper V rankHelper V
9 years ago
Solved

Replacing just 1 value in a table.

  Hello,    I have on this table a loooootttsss of "zero" values on the column "consumo", and i need to replace just that one on the screenshot for "8770". im not managing to do that, when i...
Need Help
  • BraneyBI's avatar
    BraneyBI
    9 years ago

    This can easily be done in M.  In your query, add a custom column which looks for both the zero and an ID which identifies the row.

     

    consumo_New = if ( [ID] = "whatever id" and [consumo] = "0" ) then "8770" else [consumo]

     

    Remove the original [consumo] column and rename [consumo_New] to [consumo]

     

    Later, you can update this column for any additonal updates you need to make

     

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi bolabuga,

     

    BraneyBI’s solution seems great, you can also use dax formula to replace the specify value(need a unique value to find the specify record):

     

    For example( assume HIDRO is the unique value column):

    Table = SELECTCOLUMNS(Sheet1,"Column1",[column1],"consumo",if(AND([HIDRO]=”Y09F266296”,[consumo]=0),8770,[consumo]),"Column2",[column2], "Column3",[column3],…)

     

    Reference:

    SELECTCOLUMNS Function (DAX)

     

    Notice: dax query can’t replace current table’s value, you can create a new table to store these values.

     

    Regards,

    Xiaoxin Sheng

     

BraneyBI's avatar
BraneyBI
Icon for Kudo Commander rankKudo Commander
9 years ago

This can easily be done in M.  In your query, add a custom column which looks for both the zero and an ID which identifies the row.

 

consumo_New = if ( [ID] = "whatever id" and [consumo] = "0" ) then "8770" else [consumo]

 

Remove the original [consumo] column and rename [consumo_New] to [consumo]

 

Later, you can update this column for any additonal updates you need to make

 

  • bolabuga's avatar
    bolabuga
    Icon for Helper V rankHelper V
    9 years ago

    Braney i had time to test it today, and it worked pretty well, im posting the result for future references.

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi BraneyBI

     

    Even after doing as advised my value is still not getting replaced! (even after adding the custom column)

     

    What could possibly be the reason ? 

     

     

     

    • bolabuga's avatar
      bolabuga
      Icon for Helper V rankHelper V
      7 years ago

      Anonymous

       

      It should work, if the combination of just 2 filters will find the "1 result" you want to replace.

       

      In my case, [the id of a specific client] where [consumo] was equals to zero, found the right value to replace on the entire table, however that could not be the case for you.

       

      If that is your problem, you should look which combination of filters (results on your table) will find just that specific result you want to change the value, maybe a specific date/month/year.

       

      for example:

       

      NEW_COLUMN = If( [ID] = "whatever id" and [year] = "whatever year" and [consumo] = 0 then "your_value" else [consumo])

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi bolabuga

         

        Thanks for the Explanation :)

         

        However it also worked when i tried with Conditional Column.