Replacing just 1 value in a table.

[bolabuga]

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 use the replace option, its changing all the zeros on my consumo column to "8770".

[BraneyBI · Accepted solution]

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

[quyenduong]

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]

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.

[Anonymous · Accepted solution]

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

[bolabuga]

Thks to both Braney and Xiao, im not being able to work these recent days on PBI, will get back ASAP. 

[BraneyBI · Accepted solution]

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]

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]

@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]

Hi @bolabuga

Thanks for the Explanation 🙂

However it also worked when i tried with Conditional Column.

[bolabuga]

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