cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Conditionally Replace Values

Hi all,

 

I have two columns consisting of article numbers and a column that indicates if the article is sold online or not. Articles sold online do not have an article number. So I want to replace the value in Article Number based on if it was a sale online or not.

 

Ths is what I am using currently which doesn't seam to work as the article number for online sales is still empty/null

 

Table.ReplaceValue(#"Uppercased Text",each [ArticleNumber],each if Text.Contains([Source.Name],"Online")=true then [LeadingArticleNumber] else [ArticleNumber],Replacer.ReplaceText,{"ArticleNumber"})

 

If I add a custom column it works exactly as I would expect it. 

= Table.AddColumn(#"Filtered Rows2", "Custom", each if Text.Contains([Source.Name],"Online")=true then [LeadingArticleNumber] else [ArticleNumber])

 

the custom coulmn now has the same value for ArtcicleNumber as the LeadingArticleNumber when it is an online sale. 

 

No I am wondering wh does the conditional replacement doesn't seam towork?

3 REPLIES 3
Super User IV
Super User IV

Please try this line if that doesn't work.  Make sure the LeadingArticleNumber column is Text (or use Replacer.ReplaceValue).

 

= Table.ReplaceValue(#"Uppercased Text","Online", each [LeadingArticleNumber], Replacer.ReplaceText,{"ArticleNumber"})

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Super User II
Super User II

Hi @dats ,

 

Difficult to say without being able to see the data that you are applying this to, but the first thing I would try is to change

'Replacer.ReplaceText,{"ArticleNumber"})' at the end of your replace step to

'Replacer.ReplaceValue,{"ArticleNumber"})'.

 

Also, FYI, you don't need "=true" after the Text.Contains() function, it evaluates to boolean.

 

Pete

hi @BA_Pete 

 

Thanks for the hint regarding "true" ou are right that was probably there from some test I made. 

I should have added that the ArticleNumber columne is a text columne. Was this what your tip is based on? But I will try regardless.

-dats

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors