Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 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

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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"

1.PNG      2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
AdministratorX
New Member

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.

v-yulgu-msft
Employee
Employee

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"

1.PNG      2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

ricardocamargos
Continued Contributor
Continued Contributor

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.