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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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