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.ReplaceValue(#"Replaced Value9","CLI","",Replacer.ReplaceText,{"AddressUnitStreetNumberRef"})

= Table.ReplaceValue(#"Replaced Value10","/","",Replacer.ReplaceText,{"AddressUnitStreetNumberRef"})

= Table.ReplaceValue(#"Replaced Value7","-","",Replacer.ReplaceText,{"AddressUnitStreetNumberRef"})

= Table.ReplaceValue(#"Replaced Value8","A","1",Replacer.ReplaceText,{"AddressUnitStreetNumberRef"})

 

Is there are way that to write one instance of code to replace the 11 individual statements? 

Need Help
  • 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.

7 Replies

    • Alice_Cooper's avatar
      Alice_Cooper
      Helper II
      3 years ago

      Not quite ...

       

      I have a list

       

      Column1       NewCoulmn            What I want to do

      C1-343          31343                    <- I look for C and Replace with 3 and replace - with ""

      23/45             2345                      <- I look for ? and replace with ""

      SECURE          999                        <- I look for SECURE and replace with 999

      34D23            34423                    <- I look for D and replace with 4

       

      In essence I want to be able to look for an individual charcter or group of chracters in the string and replace these with another set of characters ... in this case a number or ""...

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 


    • Alice_Cooper's avatar
      Alice_Cooper
      Helper II
      3 years ago

      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

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.