Forum Discussion
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...
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
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
I'm receiving an error that it can't find the column even though the column name is typed correctly. I tried this with two different attempts to replace in two different columns. Any ideas?
