cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Replacing just 1 value in a table.

Capturar.PNG

 

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".

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Kudo Commander
Kudo Commander

Re: Replacing just 1 value in a table.

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

 

View solution in original post

Highlighted
Community Support
Community Support

Re: Replacing just 1 value in a table.

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Kudo Commander
Kudo Commander

Re: Replacing just 1 value in a table.

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

 

View solution in original post

Highlighted
Community Support
Community Support

Re: Replacing just 1 value in a table.

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Highlighted
Helper V
Helper V

Re: Replacing just 1 value in a table.

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

Highlighted
Helper V
Helper V

Re: Replacing just 1 value in a table.

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

 

 Sem título.png

 

Highlighted
Frequent Visitor

Re: Replacing just 1 value in a table.

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 ? 

 

 

 

Highlighted
Helper V
Helper V

Re: Replacing just 1 value in a table.

@FathimaMoideen

 

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

Highlighted
Frequent Visitor

Re: Replacing just 1 value in a table.

Hi @bolabuga

 

Thanks for the Explanation 🙂

 

However it also worked when i tried with Conditional Column.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors