Forum Discussion

Alice_Cooper's avatar
Alice_Cooper
Helper II
3 years ago
Solved

Need Help with Replace Value

Here is a sample of the M code I have in Power BI ... there are 11 of these ... = Table.ReplaceValue(#"Renamed Columns","SECURE","999",Replacer.ReplaceText,{"AddressUnitStreetNumberRef"}) = Table.R...
  • Anonymous's avatar
    Anonymous
    3 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.