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

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