Forum Discussion
Need Help with Replace Value
- Anonymous3 years ago
Hello Alice_Cooper
I used this method to create the custom table.
BULK Replace Values in Power BI / Power Query - YouTube
Basically it involves your main query table, seconday table (TranslationsTable), custom function.let Source = #table( type table [ OldText = text, NewText = text ], { {"SECURE", "999"}, {"D", "4"}, {"/", ""}, {"C", "3"} } ) in Source(x as text) as text => let maxIterations = Table.RowCount(TranslationTable), Iterations = List.Generate( () => [ Result = Text.Replace( x, TranslationTable[OldText]{0}, TranslationTable[NewText]{0} ), Counter = 0 ], each [Counter] < maxIterations, each [ Result = Text.Replace( [Result], TranslationTable[OldText]{Counter}, TranslationTable[NewText]{Counter} ), Counter = [Counter] + 1 ], each [Result] ), output = Iterations{maxIterations - 1} in output
This method/approach will help you resolve your query.
Also, please view the video if possible to understand better.
Thank youu.
If my answer helps you, please mark it as solution.
Hello Alice_Cooper
From a quick glance it was not possible for me to know whether or not you need to change the values for multiple columns or a single column.
If you want to replace values for multiple different columns, then it is not possible. Coming to the conclusion after reading the answer from this link : Solved: Replace multiple values in multiple columns in one... - Microsoft Power BI Community
To replace in a single column, refer this link.
Solved: Replace multiple values in the same column in one ... - Microsoft Power BI Community
Thank you.
Please attach the query if you find the solution to the same.
Ok Its a single Column (Column1) I would lke to create a NewColumn as below ... The post Solved: Replace multiple values in the same column in one ... - Microsoft Power BI Community ... replaces entire text .... I want to replace a character within a string with another character ...
Column1 NewCoulmn What I want to do
C1-343 31343 <- I look for C within string and replace with 3 within string
23/45 2345 <- I look for / within string and replace with "" within string
SECURE 999 <- I look for SECURE within string and replace with 999 within string
34D23 34423 <- I look for D within string and replace with 4 within string
- Anonymous3 years agoNot applicable
Hello Alice_Cooper
I used this method to create the custom table.
BULK Replace Values in Power BI / Power Query - YouTube
Basically it involves your main query table, seconday table (TranslationsTable), custom function.let Source = #table( type table [ OldText = text, NewText = text ], { {"SECURE", "999"}, {"D", "4"}, {"/", ""}, {"C", "3"} } ) in Source(x as text) as text => let maxIterations = Table.RowCount(TranslationTable), Iterations = List.Generate( () => [ Result = Text.Replace( x, TranslationTable[OldText]{0}, TranslationTable[NewText]{0} ), Counter = 0 ], each [Counter] < maxIterations, each [ Result = Text.Replace( [Result], TranslationTable[OldText]{Counter}, TranslationTable[NewText]{Counter} ), Counter = [Counter] + 1 ], each [Result] ), output = Iterations{maxIterations - 1} in output
This method/approach will help you resolve your query.
Also, please view the video if possible to understand better.
Thank youu.
If my answer helps you, please mark it as solution.- Anonymous3 years agoNot applicable
Alice_Cooper
Brother, did this method work for you?