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
Lagoona23
Frequent Visitor

Replace only some values in a column

I'm just starting out with Power BI so any help will be most appreciated.
Is it possible to select some cells in a column and replace text only in those?
For example I have the number 12 in a number of cells in column A but I only want to change 12 to 2 for some of them based on the value of a different column. Example change 12 to 2 where month is February but not where month is March.
It doesn't seem possible to just highlight the rows I want to change and then select the replace feature as I would do in Excel.
 
Thank you.
 
1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @Lagoona23 

 

The easiest way would be to add an extra Conditional Column in Power Query/Transform data specifying your conditions.

image.png

Happy to assist further if you can provide a data sample.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Lagoona23 , In power query M you have If else

example

New Margin = ((if [Item.Brand] = "Brand 1" then 5 else
if [Item.Brand] = "Brand 2" then 5.5 else
if [Item.Brand] = "Brand 3" then 6.0 else
if [Item.Brand] = "Brand 4" then 6.5 else
7.0 )*([Qty]*[Price]) *((100-[Discount Percent])/100))/100

 

And text function https://docs.microsoft.com/en-us/powerquery-m/text-functions

 

with help of that you can do that

Mariusz
Community Champion
Community Champion

Hi @Lagoona23 

 

The easiest way would be to add an extra Conditional Column in Power Query/Transform data specifying your conditions.

image.png

Happy to assist further if you can provide a data sample.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Thank you @Mariusz this worked for me, but have since noticed that you can only include 1 conditional - eg: you can't say If column A = 5 AND Column B = 5 so for some other transformations I'm looking at other things. Having an issue with concatenating 2 columns now but will post a separate question I think.

By the way the dataset I'm trying to transform is Avi Singh's Ultimate Calendar table - I'm changing it to suit my companies fiscal year.

Ah yes thank you I hadn't found that, it looks like it could work I will try it - many thanks.

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.