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
murrayb8
Helper I
Helper I

How to create a function to search for and replace strings based on another table?

Hi, I'm trying to find a way to search for and replace multiple strings in a column of text dynamically using values from another table but im getting stuck. My data exists in two tables, one table, "Raw data", with the column of text strings, these are essentially sentences, and the other table, "Replacements", reads in from an excel file and contains two columns, one with the text to find and the other with the text to replace.

 

I found what looks to be a good post but I cant get it to work for me here: https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/

 

I've essentially gone into my Replacements table and in advanced editor I've tried to ammend in the function from the site above but I'm missing something as I get an error at the TurnTextToList section stating that a type List cant be converted to type Table.

 

Here is my code so far:

 

let
Source = Excel.Workbook(File.Contents("C:\Users\OneDrive\Sentences.xlsx"), null, true),
Replacements_Table = Source{[Item="Replacements",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Replacements_Table,{{"Text to Replace", type text}, {"Text to Add", type text}}),

CreateListOfLists = Table.AddColumn(Source, "Liste", each ({[Text To Replace], [Text to Add]})),
Text = Sentences[Original Text],
TurnTextToList = Table.AddColumn(Text, "Custom", each Text.Split([Text], " ")),
Replacements = Table.AddColumn(TurnTextToList, "Changed Text Expected",
each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste])," ")),
Cleanup = Table.RemoveColumns(Replacements,{"Custom", "Text"})
in

Cleanup

 

Any Help someone could give to help me get this working would be really appreciated!

 

Thanks

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @murrayb8,

 

I guess below blog will suitable for your requirement:
Multi Condition Logic In Power Query

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi, @murrayb8

 

Not sure on the M side, since I typically do this on the DAX side. You can either use Substitute or Replace

 

Best,
Scott

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.