Forum Discussion

Nasriq02's avatar
Nasriq02
New Member
2 years ago
Solved

How to replace value for numerical column using equation

Hello guys, I have a table that looks like this: Department Mar '23 Spend Apr '23 Spend May '23 Spend Retail 23.00 11.33 30.92 Businesses 0.70 1.56 2.33 Private 1.33 5.00 ...
Need Help
  • jennratten's avatar
    jennratten
    2 years ago

    Hello!  This will do it.

     

    et
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotSczMUdJRMjLWMzAA0oaGesbGQNrYQM/SSClWJ1rJqbQ4My+1uDi1GChsoGcOVqVnagbSBFILUhNQlFmWWJIKlgFrN4WYZqBnYaEUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Department = _t, #"Mar '23 Spend" = _t, #"Apr '23 Spend" = _t, #"May '23 Spend" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Department", type text}, {"Mar '23 Spend", type number}, {"Apr '23 Spend", type number}, {"May '23 Spend", type number}}),
    Custom2 = Table.ReplaceValue(#"Changed Type",each 1000,"",(x,y,z)=>x*y,{"Mar '23 Spend","Apr '23 Spend","May '23 Spend"})
in
    Custom2