Forum Discussion
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...
Hello Anonymous
You can achieve the above scenario using M language in Power Query.Go to Power Query Editor → select your table.
Select the “$ Net” column.
- 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.
OwenAuger
Super User
Super UserHi 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
CompanyAZeroThis replaces $ Net with 0 for Company A only.
Does something like this work for you?