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.
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
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?