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 rohit1991, this won't work in my opinion. You can't refer another column in Table.TransformColumns. That is the reason why I showed solution with Table.TransformRows.

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

Hi dufoq3,


TransformColumns cannot reference another column. Here's an alternative approach using Table.TransformRows to update the Value column without creating a new one:

let
Source = YourTable,
UpdatedTable = Table.FromRecords(
Table.TransformRows(Source, each Record.TransformFields(_, {{"Value", if _[Type] = "B" then 0 else _[Value]}}))
)
in
UpdatedTable

This method ensures the Value column is updated based on the Type without creating a new column. Let me know if you have further questions or need more clarification!