Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 someone suggest to manually modify data in Power Bi? All of this data comes from a source that cannot be modified prior to coming into Power BI. (Trying to automate a report)
Thank you,
Nick Patel
Solved! Go to Solution.
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,
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.
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?
Hi @Anonymous,
You can do it using Power Query.
Do you have any ID, so it's possible to replace the content based on it.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |