Forum Discussion

bolabuga's avatar
bolabuga
Helper 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

     

quyenduong's avatar
quyenduong
Helper II
2 years ago

Hi everyone,

 

I have the data as follows but I need to replace the first row in the column COB to become value "COB" because I need to promote the first row as Header. 

 


This value "28.02.2024" can be changed all the times depending on the report time extraction. However, if I right click on this column and replace values, it will change all of values 28.02.2024 but I only need to change the first row only. 

I google but cannot find any solution, does anyone know?

jayeshsolanki's avatar
jayeshsolanki
New Member
1 year ago

Thanks to BraneyBI's Idea.
I had similar problem like you quyenduong. Below is the solution.

Add conditional new custom column
COB_NEW= if [Column23] = "Study leave" then "COB" else [COB]
This will replace 1st row with word COB, and for rest of the rows, it will take the date from column COB
next step: remove COB column
next step: promote headers

 

It will work even if the COB column is dynamic, as far as "Study leave" falls on column23.