Change a cell without create a new colunm

[Mendes094]

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	40
D	50	50

Tks!

[v-nmadadi-msft]

Hi @Mendes094 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks ands regards

[v-nmadadi-msft]

Hi @Mendes094 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you

[v-nmadadi-msft]

Hi @Mendes094 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thanks and Regards

[rohit1991 · Accepted solution]

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]

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]

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!

[dufoq3]

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

[v-nmadadi-msft]

Hi @Mendes094  ,
Thanks for reaching out to the Microsoft fabric community forum.
I would like to thank @m_dekorte  for their suggestion
I have tried to reproduce their M query.
I have imported data from Excel and then used their suggested M query to get required output

let
    Source = Excel.Workbook(File.Contents("C:\Users\Downloads\elif_.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Type", type text}, {"Value", Int64.Type}, {"Results", Int64.Type}}),
    #"ReplaceValue" = Table.ReplaceValue(#"Changed Type",each [Value],each if [Type]="B" then 0 else [Value],Replacer.ReplaceValue,{"Value"})
in
    #"ReplaceValue"

It has successfully got the desired output 


Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to give "Kudos"
Thanks and Regards

[m_dekorte]

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