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.

m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident Rockstar
1 year ago

Hi Mendes094,

 

There are several options available, but using "Replace Values" is likely the most intuitive choice.

let
    Source = Table.FromRecords(
        {
            [Type = "A", Value = 10],
            [Type = "B", Value = 20],
            [Type = "C", Value = 40],
            [Type = "D", Value = 50]
        },  type table [Type=text, Value=number]
    ),
    ReplaceValue = Table.ReplaceValue(Source,each [Value],each if [Type]="B" then 0 else [Value],Replacer.ReplaceValue,{"Value"})
in
    ReplaceValue

 

I hope this is helpful