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:

DepartmentMar '23 SpendApr '23 SpendMay '23 Spend
Retail23.0011.3330.92
Businesses0.701.562.33
Private1.335.000.88

 

How can I replace each of the value in the column that has numerical value to the multiple of 1000 in power query.
The result should look like this:

DepartmentMar '23 SpendApr '23 SpendMay '23 Spend
Retail230001133030920
Businesses70015602330
Private13305000880


Thank you in advance

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

     

1 Reply

  • jennratten's avatar
    jennratten
    Super User
    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