Replace all values with 0 in column

[Anonymous]

Morning - I am trying to achieve something that I was hoping would be quite simple in Power Query 

I have a large data set and reports already build up, but what I need to do is change $ Net value from a specific Company to 0 for all records. 

I would like to filter on Company A and change all $ Net to 0  - ideally without making a new column. 

Company	$ Net
A	340
A	124
B	0
A	765
C	736
B	23
A	123
B	111
B	122
A	666
A	99
C	0
C	3
C	131
C	278
C	989
A	88
C	23
C	98

[SundarRaj]

Hi Robert, @Anonymous 
The best case application in your scenario should be to use Table.TransnformRows. This doesn't create an extra column and creates and if condition as well. I'll leave the image of the ouput and the code for it. Let me know if you need the reference file as well. Thanks!

Code:
let
Source = [SourceTable],
TransformRows = Table.TransformRows ( Source , each _ & [ #"$ Net" = if [Company] = "A" then 0 else [#"$ Net"] ] ),
FromRec = Table.FromRecords ( TransformRows )
in
FromRec

[v-tejrama]

Hi @Anonymous ,

Thank you @Anonymous and @ANVS for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[ANVS · Accepted solution]

Hello @Anonymous 

You can achieve the above scenario using M language in Power Query.

1. Go to Power Query Editor → select your table.

2. Select the “$ Net” column.

3. Go to the Formula Bar and replace the current step for $ Net with this custom logic as follows:

= Table.TransformColumns(
PreviousStepName,
{{"$ Net", each if [Company] = "A" then 0 else _, type number}}
)

Hope, I answered/resolved your query. Please give some kudos and mark this post as "Accepted Solution" if you find it useful.

[ronrsnfld]

Did you test it? I could not get it to work. I did not think you could refer to a different column within  Table.TransformColumns transform operation.

[v-tejrama]

Hi @Anonymous ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[OwenAuger]

Hi @Anonymous 

You can use Table.ReplaceValue with appropriate functions for the 2nd and 3rd arguments. Here's an example using your sample table:

let
  Source = #table(
    type table [Company = text, #"$ Net" = number],
    {
      {"A", 340},
      {"A", 124},
      {"B", 0},
      {"A", 765},
      {"C ", 736},
      {"B", 23},
      {"A", 123},
      {"B", 111},
      {"B", 122},
      {"A", 666},
      {"A", 99},
      {"C ", 0},
      {"C ", 3},
      {"C ", 131},
      {"C ", 278},
      {"C ", 989},
      {"A", 88},
      {"C ", 23},
      {"C ", 98}
    }
  ),
  CompanyAZero = Table.ReplaceValue(
    Source,
    each [#"$ Net"],
    each if [Company] = "A" then 0 else [#"$ Net"],
    Replacer.ReplaceValue,
    {"$ Net"}
  )
in
  CompanyAZero

This replaces $ Net with 0 for Company A only.

Does something like this work for you?