Home » Multi Replacement!

Multi Replacement!

Solving Multi Replacement challenge by Power Query, Power BI, Excel, Python and R

Replace “q” and “000” in the Product ID and Customer ID columns with “-“.

📌 Challenge Details and Links
Challenge Number: 115
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Multi Replacement! with Power Query

Power Query solution 1 for Multi Replacement!, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.ReplaceValue(
    Source, 
    "000", 
    "q", 
    (x, y, z) => Text.Replace(Text.Replace(x, y, z), z, "-"), 
    {"Product ID", "Customer ID"}
  )
in
  S
Power Query solution 2 for Multi Replacement!, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  lista = {{"q", "-"}, {"000", "-"}}, 
  res = List.Accumulate(
    lista, 
    Fonte, 
    (s, c) => Table.ReplaceValue(s, c{0}, c{1}, Replacer.ReplaceText, {"Customer ID", "Product ID"})
  )
in
  res
Power Query solution 3 for Multi Replacement!, proposed by Ramiro Ayala Chávez: 
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
T = Table.ReplaceValue, R = Replacer.ReplaceText,
a = T(S,"000","-",R,{"Product ID"}),
b = T(a,"q","-",R,{"Product ID"}),
c = T(b,"000","-",R,{"Customer ID"}),
Sol = T(c,"q","-",R,{"Customer ID"})
in
Sol
Power Query solution 4 for Multi Replacement!, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Replace = {"q", "000"}, 
  Return = List.Accumulate(
    Replace, 
    Source, 
    (x, y) => Table.ReplaceValue(x, y, "-", Replacer.ReplaceText, {"Product ID", "Customer ID"})
  )
in
  Return
Power Query solution 5 for Multi Replacement!, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Replace = List.Transform(
    Table.ToColumns(Source), 
    each List.Accumulate(
      {"q", "000"}, 
      _, 
      (s, c) => List.Transform(s, (x) => try Text.Replace(x, c, "-") otherwise x)
    )
  ), 
  Sol = Table.FromColumns(Replace, Table.ColumnNames(Source))
in
  Sol
Power Query solution 6 for Multi Replacement!, proposed by Abdallah Ally: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  f = each Text.Replace(Text.Replace(_, "000", "-"), "q", "-"), 
  Result = Table.TransformColumns(
    Source, 
    {{"Date", each Date.From(_), type date}, {"Product ID", each f(_)}, {"Customer ID", each f(_)}}
  )
in
  Result
Power Query solution 7 for Multi Replacement!, proposed by Abdallah Ally: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.TransformColumns(
    Source, 
    {
      {"Date", each Date.From(_), type date}, 
      {"Product ID", each Text.Replace(Text.Replace(_, "000", "-"), "q", "-")}, 
      {"Customer ID", each Text.Replace(Text.Replace(_, "000", "-"), "q", "-")}
    }
  )
in
  Result
Power Query solution 8 for Multi Replacement!, proposed by Abdallah Ally: 
let
  Source    = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Transform = Table.TransformColumnTypes(Source, {"Date", type date}), 
  Replace   = Table.ReplaceValue(Transform, "000", "-", Text.Replace, {"Product ID", "Customer ID"}), 
  Result    = Table.ReplaceValue(Replace, "q", "-", Text.Replace, {"Product ID", "Customer ID"})
in
  Result
Power Query solution 9 for Multi Replacement!, proposed by Kris Jaganah: 
let
  A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  B = List.Accumulate(
    {"000", "q"}, 
    A, 
    (x, y) => Table.ReplaceValue(x, y, "-", Replacer.ReplaceText, {"Product ID", "Customer ID"})
  )
in
  B
Power Query solution 10 for Multi Replacement!, proposed by Yaroslav Drohomyretskyi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = List.Accumulate(
    {"q", "000"}, 
    Source, 
    (currentTable, valueToReplace) =>
      Table.ReplaceValue(
        currentTable, 
        valueToReplace, 
        "-", 
        Replacer.ReplaceText, 
        {"Product ID", "Customer ID"}
      )
  )
in
  Result
Power Query solution 11 for Multi Replacement!, proposed by Yaroslav Drohomyretskyi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  q      = Table.ReplaceValue(Source, "q", "-", Replacer.ReplaceText, {"Product ID", "Customer ID"}), 
  #"000" = Table.ReplaceValue(q, "000", "-", Replacer.ReplaceText, {"Product ID", "Customer ID"})
in
  #"000"
Power Query solution 12 for Multi Replacement!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T1 = Table.TransformColumns(
    Source, 
    {
      {"Product ID", each Text.Replace(_, "000", "-")}, 
      {"Customer ID", each Text.Replace(_, "000", "-")}
    }
  ), 
  T2 = Table.TransformColumns(
    T1, 
    {
      {"Product ID", each Text.Replace(_, "q", "-")}, 
      {"Customer ID", each Text.Replace(_, "q", "-")}
    }
  )
in
  T2
Power Query solution 13 for Multi Replacement!, proposed by Ahmed Ariem: 
let
  f      = (x) => Text.Combine(Splitter.SplitTextByAnyDelimiter({"000", "q"})(x), "-"), 
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Trans  = Table.TransformColumns(Source, {"Product ID", f})
in
  Trans
Power Query solution 14 for Multi Replacement!, proposed by Ümit Barış Köse, MSc: 
let
  source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  ReplaceValues = (input as text) as text => Text.Replace(Text.Replace(input, "000", "-"), "q", "-"), 
  UpdatedTable = Table.TransformColumns(
    source, 
    {{"Product ID", ReplaceValues}, {"Customer ID", ReplaceValues}}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(UpdatedTable, {{"Date", type date}})
in
  #"Changed Type"
Power Query solution 15 for Multi Replacement!, proposed by Glyn Willis: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ReplaceCustomerID = (x, y, z) =>
    if y then
      let
        splitter = Splitter.SplitTextByAnyDelimiter({"q", "000"})(z), 
        result   = Text.Combine(splitter, "-")
      in
        result
    else
      x, 
  #"Replaced Value" = Table.ReplaceValue(
    Source, 
    each true, 
    each [Customer ID], 
    ReplaceCustomerID, 
    {"Customer ID"}
  )
in
  #"Replaced Value"

Solving the challenge of Multi Replacement! with Excel

Excel solution 1 for Multi Replacement!, proposed by 🇰🇷 Taeyong Shin: 
=HSTACK(
    B2:B9,
    REGEXREPLACE(
        C2:D9,
        "q|000",
        "-"
    ),
    E2:E9
)
Excel solution 2 for Multi Replacement!, proposed by Aditya Kumar Darak 🇮🇳: 
=SUBSTITUTE(
    SUBSTITUTE(
        B2:E9,
         "000",
         "-"
    ),
     "q",
     "-"
)
Excel solution 3 for Multi Replacement!, proposed by Oscar Mendez Roca Farell: 
=REDUCE(
    B2:E9,
    {"q",
    "000"},
     LAMBDA(
         i,
         x,
         IF(
             N(
                 +i
             ),
             i,
             SUBSTITUTE(
                 i,
                 x,
                 "-"
             )
         )
     )
)
Excel solution 4 for Multi Replacement!, proposed by Julian Poeltl: 
=HSTACK(
    B2:B9,
    SUBSTITUTE(
        SUBSTITUTE(
            C2:D9,
            "q",
            "-"
        ),
        "000",
        "-"
    ),
    E2:E9
)
Excel solution 5 for Multi Replacement!, proposed by Kris Jaganah: 
=REDUCE(
    B2:E9,
    {"q",
    "000"},
    LAMBDA(
        x,
        y,
        SUBSTITUTE(
            x,
            y,
            "-"
        )
    )
)
Excel solution 6 for Multi Replacement!, proposed by Imam Hambali: 
=VSTACK(
    B2:E2,
     HSTACK(
         B3:B9,
          REDUCE(
              C3:D9,
              {"q",
              "000"},
              LAMBDA(
                  x,
                  y,
                   SUBSTITUTE(
                       x,
                       y,
                       "-"
                   )
              )
          ),
          E3:E9
     )
)
Excel solution 7 for Multi Replacement!, proposed by Yaroslav Drohomyretskyi: 
=LET(
    Date;
    B2:B9;
    ID;
    SUBSTITUTE(
        SUBSTITUTE(
            C2:D9;
            "q";
            "-"
        );
        "000";
        "-"
    );
    Sales;
    E2:E9;
    HSTACK(
        Date;
        ID;
        Sales
    )
)
Excel solution 8 for Multi Replacement!, proposed by Sunny Baggu: 
=HSTACK(     B3:B9,     SUBSTITUTE(
         SUBSTITUTE(
             C3:D9,
              "q",
              "-"
         ),
          "000",
          "-"
     ),     E3:E9)
Excel solution 9 for Multi Replacement!, proposed by Sunny Baggu: 
=LET(     a,
     REDUCE(          B2:E9,          {"000",
          "q"},          LAMBDA(
              a,
               v,
               SUBSTITUTE(
                   a,
                    v,
                    "-"
               )
          )     ),     IFERROR(
         1 * a,
          a
     ))
Excel solution 10 for Multi Replacement!, proposed by Alejandro Campos: 
=LET(
date;
    B3:B9;Ids;
    C3:D9;tot;
    E3:E9;sustIds;
    REDUCE(Ids;
    M3:M4;
    LAMBDA(a;
    v;
    SUSTITUIR(a;
    @+(L4:v);
    v)));APILARH(
    date;
    sustIds;
    tot
))
Excel solution 11 for Multi Replacement!, proposed by Andy Heybruch: 
=HSTACK(
    B3:B9,
    REDUCE(
        C3:D9,
        {"000",
        "q"},
        LAMBDA(
            a,
            v,
            SUBSTITUTE(
                a,
                v,
                "-"
            )
        )
    ),
    E3:E9
)
Excel solution 12 for Multi Replacement!, proposed by Asheesh Pahwa: 
=REDUCE(
    B2:E9,
    {"000",
    "q"},
    LAMBDA(
        x,
        y,        SUBSTITUTE(
            x,
            y,
            "-"
        )
    )
)
Excel solution 13 for Multi Replacement!, proposed by Bilal Mahmoud kh.: 
=SCAN(
    ,
    B1:E8,
    LAMBDA(
        x,
        y,
        SUBSTITUTE(
            SUBSTITUTE(
                y,
                "q",
                "-"
            ),
            "000",
            "-"
        )
    )
)
Excel solution 14 for Multi Replacement!, proposed by Eddy Wijaya: 
=MAP(
    C3:D9,
    LAMBDA(
        m,        LET(
            
            r,
            {"q",
            "000"},
            
            f,
            SUBSTITUTE(
                m,
                r,
                "-"
            ),
            
            TAKE(
                TOROW(
                    IF(
                        FIND(
                            "-",
                            f
                        )>0,
                        f,
                        ""
                    ),
                    2
                ),
                ,
                1
            )
            
        )
    )
)
Excel solution 15 for Multi Replacement!, proposed by Hamidi Hamid: 
=SUBSTITUTE(
    SUBSTITUTE(
        D3:D9,
        "q",
        "-"
    ),
    "000",
    "-"
)
Excel solution 16 for Multi Replacement!, proposed by Hamidi Hamid: 
=HSTACK(
    B3:C9,
    SUBSTITUTE(
        SUBSTITUTE(
            c 3:D9,
            {"q"},
            "-"
        ),
        "000",
        "-"
    ),
    E3:E9
)
Excel solution 17 for Multi Replacement!, proposed by Hussein SATOUR: 
=LET(
    a,
    SUBSTITUTE(
        SUBSTITUTE(
            B3:E9,
            "q",
            "-"
        ),
        "000",
        "-"
    ),
    IFERROR(
        --a,
        a
    )
)
Excel solution 18 for Multi Replacement!, proposed by Milan Shrimali: 
=let(
    a,
    B3:E9,
    colm1,
    map(
        choosecols(
            a,
            2
        ),
        lambda(
            x,
            substitute(
                SUBSTITUTE(
                    x,
                    "000",
                    "-"
                ),
                "q",
                "-"
            )
        )
    ),
    colm2,
    map(
        choosecols(
            a,
            3
        ),
        lambda(
            x,
            substitute(
                SUBSTITUTE(
                    x,
                    "000",
                    "-"
                ),
                "q",
                "-"
            )
        )
    ),
    hstack(
        choosecols(
            a,
            1
        ),
        colm1,
        colm2,
        choosecols(
            a,
            4
        )
    )
)
Excel solution 19 for Multi Replacement!, proposed by Nicolas Micot: 
=ASSEMB.H(
    B3:B9;
    REDUCE(
        C3:D9;
        {"000";
        "q"};
        LAMBDA(
            l_valeur;
            l_cherche;
            SUBSTITUE(
                l_valeur;
                l_cherche;
                "-"
            )
        )
    );
    E3:E9
)
Excel solution 20 for Multi Replacement!, proposed by Nikola Z Grujicic – Nikola Ž Grujičić: 
=LET(
    a,
     B3:E9,
     fun,
     LAMBDA(
         x,
          LET(
              xx,
               SUBSTITUTE(
                   x,
                    "000",
                    "-"
               ),
               SUBSTITUTE(
                   xx,
                    "q",
                    "-"
               )
          )
     ),
     HSTACK(
         CHOOSECOLS(
             a,
              1
         ),
          fun(
              CHOOSECOLS(
                  a,
                   2
              )
          ),
          fun(
              CHOOSECOLS(
                  a,
                   3
              )
          ),
          CHOOSECOLS(
              a,
               4
          )
     )
)
Excel solution 21 for Multi Replacement!, proposed by Pierluigi Stallone: 
=LET(
    ids,
    A3:D9,
    pos000,
    SEARCH(
        "000",
        ids
    ),
    posq,
    SEARCH(
        "q",
        ids
    ),
    IF(
        ISNUMBER(
            pos000
        ),
        REPLACE(
            ids,
            pos000,
            3,
            "-"
        ),
        IF(
            ISNUMBER(
                posq
            ),
            REPLACE(
                ids,
                posq,
                1,
                "-"
            ),
            ids
        )
    )
)
Excel solution 22 for Multi Replacement!, proposed by Pieter de B.: 
=REDUCE(
    B2:E9,
    {"q",
    "000"},
    LAMBDA(
        x,
        y,
        IF(
            N(
                +x
            ),
            x,
            SUBSTITUTE(
                x,
                y,
                "-"
            )
        )
    )
)
Excel solution 23 for Multi Replacement!, proposed by Rick Rothstein: 
=HSTACK(
    B2:B9,
    SUBSTITUTE(
        SUBSTITUTE(
            C2:D9,
            "q",
            "-"
        ),
        "000",
        "-"
    ),
    E2:E9
)

Solving the challenge of Multi Replacement! with Python

Python solution 1 for Multi Replacement!, proposed by Konrad Gryczan, PhD: 
import pandas as pd

path = "CH-115 Multi Replacement.xlsx"
input = pd.read_excel(path, usecols="B:E", skiprows=1)
test = pd.read_excel(path, usecols="G:J", skiprows=1).rename(columns=lambda x: x.replace(".1", ""))

result = input.replace({"0{3}|q": "-"}, regex=True)

print(result.equals(test))  # True
Python solution 2 for Multi Replacement!, proposed by Luan Rodrigues: 
import pandas as pd
file = "CH-115 Multi Replacement.xlsx"
df = pd.read_excel(file,usecols="B:E",skiprows=1)
lista = [
 ('q', '-'),
 ('000', '-')
]
colunas = df.columns
for old_value, new_value in lista:
 for col in colunas:
 if df[col].dtype == 'object':
 df[col] = df[col].str.replace(old_value, new_value, regex=False)

print(df)

Solving the challenge of Multi Replacement! with Python in Excel

Python in Excel solution 1 for Multi Replacement!, proposed by Abdallah Ally: 
df = xl("B2:E9", headers=True)

# Perform data wrangling
df[['Product ID', 'Customer ID']] = (
 df[['Product ID', 'Customer ID']]
 .map(lambda x: x.replace('000', '-').replace('q', '-'))
)

# Display the final results
df
Python in Excel solution 2 for Multi Replacement!, proposed by Alejandro Campos: 
df = xl("B2:E9", headers=True).replace({'q': '-', '000': '-'}, regex=True)
Python in Excel solution 3 for Multi Replacement!, proposed by Ümit Barış Köse, MSc: 
df = xl("B2:E9", headers=True)
replace_map = {
 '000': '-',
 'q': '-'
}
for col in ['Product ID', 'Customer ID']:
 for old, new in replace_map.items():
 df[col] = df[col].str.replace(old, new, regex=False)
df

Solving the challenge of Multi Replacement! with R

R solution 1 for Multi Replacement!, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)

path = "files/CH-115 Multi Replacement.xlsx"
input = read_excel(path, range = "B2:E9")
test = read_excel(path, range = "G2:J9")

result = input %>%
 mutate(across(c(`Product ID`, `Customer ID`), ~str_replace(., "0{3}|q", "-")))

identical(result, test)
#> [1] TRUE

Solving the challenge of Multi Replacement! with Google Sheets

Google Sheets solution 1 for Multi Replacement!, proposed by Peter Krkos: 
PowerQuery Solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?gid=630999189#gid=630999189

Solving the challenge of Multi Replacement! with SQL

SQL solution 1 for Multi Replacement!, proposed by Ümit Barış Köse, MSc: 
hashtag
#DAX in 
hashtag
#PowerBI Solution

UpdatedTable = 
SELECTCOLUMNS(
 Table1,
 "Date", [Date],
 "Product ID", SUBSTITUTE(SUBSTITUTE([Product ID], "000", "-"), "q", "-"),
 "Customer ID", SUBSTITUTE(SUBSTITUTE([Customer ID], "000", "-"), "q", "-"),
 "Total Sales", [Total Sales]
)

Leave a Reply