Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 months ago
Solved

Replace all values with 0 in column

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
A340
A124
B0
A765
C 736
B23
A123
B111
B122
A666
A99
C 0
C 3
C 131
C 278
C 989
A88
C 23
C 98
Need Help
  • ANVS's avatar
    ANVS
    9 months ago

    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.

6 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    9 months ago

    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?

  • ANVS's avatar
    ANVS
    Advocate II
    9 months ago

    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's avatar
      ronrsnfld
      Super User
      9 months ago

      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's avatar
        v-tejrama
        Community Support
        9 months ago

        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.

         

  • v-tejrama's avatar
    v-tejrama
    Community Support
    9 months ago

    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!

  • SundarRaj's avatar
    SundarRaj
    Super User
    9 months ago

    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