Replace value in table based off of a greater than statement

[russellbruce]

I have a table where I want to replace vaules in Collumn A if the vaule is greater than 480 and I want it to change to 480

The vaule in this collumn can not go over 480

In Power Query 

[adudani · Accepted solution]

hi @russellbruce ,

use add a  custom column 

type this in:

if [Collumn A] > 480 then 480 else [Collumn A]

click ok.

Let me know if this resolves the issue.

Appreciate a thumbs up if this is helpful.

[edhans · Accepted solution]

Use this logic:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcvBDQAgCAPAXXj7QAsIsxD2X8MEfF1KSyYdXKqVdLe1ptKqe+uMyRjFogXz5Jid/B4xd9v8/5WqHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Data", Order.Descending}}),
    #"Replaced Value" = 
        Table.ReplaceValue(
            #"Sorted Rows",
            each if [Data] > 480 then [Data] else false,
            each 480,
            Replacer.ReplaceValue,
            {"Data"}
        )
in
    #"Replaced Value"

If the value is over 480, then it returns the value of the column. If it isn't it evaluates to false and leaves the Table.ReplaceValues function.

If it returned the value of the Data field, it replaces it with 480 regardless of what it is (but your first condition said only return it if it is over 480)

So this:

Becomes this:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[adudani · Accepted solution]

hi @russellbruce ,

use add a  custom column 

type this in:

if [Collumn A] > 480 then 480 else [Collumn A]

click ok.

Let me know if this resolves the issue.

Appreciate a thumbs up if this is helpful.