Forum Discussion

Mendes094's avatar
Mendes094
New Member
1 year ago
Solved

Change a cell without create a new colunm

Hello,  I have a table and I need change the value for 0 when Type is B. How can I do this without create a new colunm in Power query?   Type Value Results A 10 10 B 20 0 C 40 ...
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    Hi Mendes094 ,

    To update Value directly without a new column:

    let
    Source = YourTable,
    UpdatedTable = Table.TransformColumns(Source, {{"Value", each if [Type] = "B" then 0 else _, type number}})
in
    UpdatedTable

    This modifies Value based on Type without adding a new column.

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
1 year ago

Hi Mendes094, another solution:

 

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrViVZyAjKNIExnINMEwnQBMk2BzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Value", type number}}),
    ReplacedValue = Table.FromRecords(Table.TransformRows(ChangedType, each _ & (if [Type] = "B" then [Value = 0] else [])), Value.Type(Table.FirstN(ChangedType, 0)))
in
    ReplacedValue