Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

How to replace = with contains in power query without having write miles of code

I had a prefix added to the data I rely on to categorise a few columns of data and it has broken my dashboard.

Whereas it previously was 'Site1' it now reads 'PREFIX123 - Site1'

My query is layed out below.

= Table.AddColumn (#"Filtered Rows1", "Region", each if [Site] = "Site1" then "Region1"

else if [Site] = "Site2" then "Region2"

else "CHECK")

Is there any way I can replace the equals function with a contains function to circumvent the added prefix or is this a lost cause?

Anonymous
4 years ago
Lost cause?! This is what Power Query was made for!

You can either split off that prefix altogether using split by delimiter " - " or by using each if Text.Contains([Site], "Site 1") then ...

But I would use the split, so you don't have to rewrite all the other formulas to Text.Contains.

--Nate

3 Replies

Anonymous
Not applicable
4 years ago
Lost cause?! This is what Power Query was made for!

You can either split off that prefix altogether using split by delimiter " - " or by using each if Text.Contains([Site], "Site 1") then ...

But I would use the split, so you don't have to rewrite all the other formulas to Text.Contains.

--Nate
- Anonymous
  Not applicable
  4 years ago
  Far out, such a simple solution but a perfect one.
  
  My concern was having to replace all my code which is not crazy difficult but annoying and was hoping there were more efficient habits to get into to manage this kind of stuff.
  
  Soo completely obvious now, thank you.
Greg_Deckler
Community Champion
4 years ago
Anonymous

if Text.Contains([ColumnName], "A") then "A" else "B"