Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Edit Single Cell in Power BI

Hello,   I am trying to edit the data through Power BI Desktop.  We have a lot of data that needs manually modified, but it seems the replace function does not work on a single cell.  How would som...
General Comment
Need Help
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    If you can specify the single cell with a certain rule, you can replace the value with a IF statement to avoid replacing all data values.

     

    In below example, I replace the "PO" in third row where [Name] is Josh.

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
    data_Sheet = Source{[Item="data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Type of Product", type text}, {"Location", type text}, {"Start Date", type date}, {"End Date", type date}, {"Net Rev(USD)", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","PO", each if [Name]="Josh" then "HH" else "PO" ,Replacer.ReplaceText,{"Type of Product"})

in
    #"Replaced Value"

         

     

    Best regards,

    Yuliana Gu

AdministratorX's avatar
AdministratorX
New Member
2 years ago

Hi,
I found an issue today where I had used the "Replace Values" to change the cells in a column, a couple of days ago. Unforturnately, I changed it to something that was already there - totally in error. And then made successive changes so now I had over 200 steps when cleaning the data.
Anyways, when you click on each step it will show you the code of the action that took place. You can then double-click on the step to change what took place. So the "replace-values" box will come up already populated. You just need to go in and change the "Replace-with" box to what it should be and then "life-is-good" again.