Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
E_K_
Helper III
Helper III

What is an efficient strategy to replace values in multiple columns (including Text.Contains)?

I have 3 columns that need almost all values changed and my current (bad) approach is to have 3 very very conditional column replacements with far too many clauses. This creates 3 output columns. There is no easy referent or lookup.

Worth noting that the 3 columns being changed have many values - 

[Change Group] - has about 40 odd values to replace

[Implementer Teams] has about 80 values to replace (this is due to the fact that it is a comma delimited list that I have split to have one row per line)

[Checkout Teams] has about 120 values to replace (more values available in this field).

Here is what a couple of rows of data looks like in list form in [Implementer Teams] and [Checkout Teams] and the goal below. This should be split out so we have a line per row for filtering on each stage in the front end:

List form 

Change GroupImplementer TeamsCheckout Teams
ABC-Pop-HS-Bouillabaise HosTimengABC-Pop-HS-Bouillabaise HosTimeng, Alts, Managed FlowersABC-Prod Eng-Inv & Trad-Risk, Managed Flowers, ABC-Pop-HS-Bouillabaise HosTimeng
Managed FlowersAlts-banana, Bouillabaise Hostimeng, Managed Flowers, Managed Flowers, ABC-Prod-ENT

 

After hunners of replacements

Change GroupImplementer TeamsCheckout Teams
Alpha Beta-Pop-HS-Bouillabaise HosTimengAlpha Beta-Pop-HS-Bouillabaise HosTimeng, Alpha Beta-Pop-MC-Alts, Alpha Beta-Pop-MC-Protime OpsAlpha Beta-Pop-PO-Product Office, Alpha Beta-Pop-MC-Protime Ops, Alpha Beta-Pop-HS-Bouillabaise HosTimeng
Alpha Beta-Pop-MC-Protime OpsAlpha Beta-Pop-MC-Alts-Flipper, Alpha Beta-Pop-MC-Protime OpsAlpha Beta-Pop-MC-Protime Ops, Alpha Beta-Prod-ENT-Enterprise

 

To clarify, by values I am not talking about row count (which is probably around 1 million).

In theory, If I could get a step that could read  value & parts of values  in [Checkout Teams], [Implementer Teams], and [Change Group] (some clauses use Text.Contains or Text.StartsWith as its consolidating a lot of historical teams names) and do a 120 step rule to replace them all with the current ame. 

 

Attached is the code I am employing (like a fool) to get this done. There has to be a better way (that loads!)

 

#"Added CondiTimeonal Column4" = Table.AddColumn(#"Added CondiTimeonal Column", "Change Group", each if Text.Contains([group], "Vulnerability RemediaTimeon") then "Apple Banana-Pop-HS-Infra Time-Sys Ops"  else if Text.Contains([group], "AIM") then "Info Sec-Security-AIM" else if Text.Contains([group], "PCE")  then "Apple Banana-Pop-PO-Proactive Office" else if [group] = "AutomaTimeon TesTimeng" then "Apple Banana-Pop-ES-Quality Assurance" else if [group] = "Site Reliability Engineering" then "Apple Banana-Pop-ES-Site Reliability Engineering" else if [group] = "ABC-Pop-PC-Cloud Engineering" then "Apple Banana-Pop-HS-Infra Time-Cloud" else if [group] = "Apple Banana-Pop-HS-Information Time-Sys Ops" then "Apple Banana-Pop-HS-Infra Time-Sys Ops" else if Text.Contains([group], "Alts Ops") then "Apple Banana-Pop-MC-Alts Ops" else if Text.Contains([group], "Prod Ops") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if Text.Contains([group], "CCC") then "Apple Banana-Pop-MC-Silly Banana" else if Text.Contains([group], "Time-Cloud") then "Apple Banana-Pop-HS-Infra Time-Cloud" else if [group] = "ABC-Pop-PO-System Ops" then "Apple Banana-Pop-HS-Infra Time-Sys Ops" else if [group] = "ABC-Pop-HS-Infra Time-Sys Ops" then "Apple Banana-Pop-HS-Infra Time-Sys Ops" else if [group] = "ABC-Pop-HS-Information Time-Sys Ops" then "Apple Banana-Pop-HS-Infra Time-Sys Ops" else if  Text.Contains([group], "Core Systems") then "Apple Banana-Pop-HS-Infra Time-Sys Ops" else if [group] = "Bouillabaise OperaTimeons" then "Apple Banana-Pop-HS-Bouillabaise Time" else if [group] = "ABC-Pop-HS-Bouillabaise Time" then "Apple Banana-Pop-HS-Bouillabaise Time" else if [group] = "Web OperaTimeons" then "Apple Banana-Pop-HS-Web Time" else if [group] = "ABC-Pop-HS-Web Time" then "Apple Banana-Pop-HS-Web Time" else if Text.Contains([group], "Prod Ops") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if Text.Contains([group], "Pop-MC-Managed Flowers") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if Text.Contains([group], "PCE") then "Apple Banana-Pop-PO-Proactive Office" else if Text.Contains([group], "Proactive Office") then "Apple Banana-Pop-PO-Proactive Office" else if Text.Contains([group],"Messaging") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if  Text.Contains([group],"SWIFT Support") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if  Text.Contains([group],"Middleware") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if Text.Contains([group], "Business AnalyTimecs and Enterprise Technologies") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if Text.Contains([group], "Market Data") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if [group] = "Prod Ops - New Technology" then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if Text.Contains([group], "Hashicorp Vault") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if [group] = "ABC-Pop-PO-Alts Ops" then "Apple Banana-Pop-MC-Alts Ops" else if [group] = "PS-CCC" then "Apple Banana-Pop-MC-Silly Banana" else if Text.Contains([group], "Market Data") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if Text.Contains([group], "Directory Flowers") then "Apple Banana-Pop-Time-Engineering" else if Text.Contains([group], "Managed Flowers") then "Apple Banana-Pop-MC-Proactiveimeon Ops" else if Text.Contains([group], "Virtual") then "Apple Banana-Pop-MC-Proactiveimeon Engineering" else if Text.Contains([group], "Corporate ApplicaTimeons") then "Apple Banana-Prod-ENT-Enterprise" else if Text.Contains([group], "Cloud Flowers") then "Apple Banana-Pop-Time-Enterprise Cloud Flowers" else if Text.Contains([group], "Security Architecture") then "Apple Banana-Pop-MC-Proactiveimeon Engineering" else if Text.Contains([group], "DOE") then "Apple Data-DE-Data Engineering" else if Text.Contains([group], "Enterprise Messaging") then "Tech & Ops-TP-DW-User Experience OperaTimeons" else if Text.Contains([group], "AV") then "Tech & Ops-TP-DW-Digital Workplace" else if Text.Contains([group], "UC") then "Tech & Ops-TP-DW-Unified Comms Engineering" else if Text.Contains([group], "Mobility") then "Tech & Ops-TP-DW-User Experience OperaTimeons" else if Text.Contains([group], "Desktop Engineering") then "Tech Pops-DW-Modern Workplace Engineering" else if Text.Contains([group], "Tech Pops-Time-Engineering") then "Apple Banana-Pop-Time-Engineering" else if Text.Contains([group], "Time-IE-Engineering") then "Apple Banana-Pop-Time-Engineering" else if Text.Contains([group], "Storage") then "Apple Banana-Pop-Time-Ops-Storage" else if Text.Contains([group], "Time-Ops-Pop") then "Apple Banana-Pop-Time-Ops-Pop" else if Text.Contains([group], "Ops-Network") then "Apple Banana-Pop-Time-Ops-Network" else if Text.Contains([group], "CriTimecal Site") then "Apple Banana-Pop-Time-CriTimecal Site OperaTimeons" else if Text.Contains([group], "Time-Network Engineering" ) then "Apple Banana-Pop-Time-Ops-Network" else if Text.Contains([group], "Digital User Exp") then "Tech & Ops-TP-DW-User Experience OperaTimeons" else if Text.Contains([group], "InfoSec Ops") then "Tech & Ops-InformaTimeon Security-Security" else if Text.Contains([group], "GMC") then "Corporate CommunicaTimeons" else if [group] = "Client Flowers" then "Apple Client Business-Apple Client Flowers" else [group]),
    Custom4 = Table.AddColumn(#"Added CondiTimeonal Column4", "Checkout Group", 
    each if [Checkout Teams] = "" then [Change Group] else if 
    Text.Contains([Checkout Teams], "AIM") then "Info Sec-Security-AIM" 
    else if Text.Contains([Checkout Teams], "APM") then "Apple Proactive-APM-Proactive Management" 
    else if Text.Contains([Checkout Teams], "Client Exp Tech Portfolio Flowers") then "Apple Banana-Prod-PTA-Portfolio Flowers" 
    else if Text.Contains([Checkout Teams], "ABC-CSS-Client Exp Tech Service") then "Apple Banana-Prod-CP-Client Exp Tech Service" 
    else if Text.Contains([Checkout Teams], "ABC-CSS-Sales & Service Enablement Engineering") then "Apple Banana-Prod-CP-Sales & Service Engineering"
    else if Text.Contains([Checkout Teams], "ABC-CSS-Sales & Service Enablement Proactive Mgmt") then "Apple Banana-Prod-CP-Client Exp Tech Proactive Mgmt"
    else if Text.Contains([Checkout Teams], "ABC-Pop-ES") then "Apple Banana-Pop-ES-Developer Systems"
    else if Text.Contains([Checkout Teams], "FS-CriTimecal Information") then "Apple Banana-Pop-FS-CriTimecal Information"
    else if Text.Contains([Checkout Teams], "App Time") then "Apple Banana-Pop-HS-App Time"
    else if Text.Contains([Checkout Teams], "Vulnerability RemediaTimeon") then "Apple Banana-Pop-HS-Infra Time-Sys Ops" 
    else if Text.Contains([Checkout Teams], "PCE")  then "Apple Banana-Pop-PO-Proactive Office" 
    else if Text.Contains([Checkout Teams], "Pop Availability") then "Apple Banana-Pop-MC-Silly Banana"
    else if Text.Contains([Checkout Teams], "ABC-Pop-OperaTimeons") then "Apple Banana-Pop-MC-Proactiveimeon Ops"
    else if Text.Contains([Checkout Teams], "ABC-Pop-PC-Cirrus") then "Apple Banana-Pop-HS-Infra Time-Cloud"
    else if Text.Contains([Checkout Teams], "Alts") then "Apple Banana-Pop-MC-Alts Ops"
    else if Text.Contains([Checkout Teams], "SRE") then "Apple Banana-Pop-ES-Developer Systems"
    else if Text.Contains([Checkout Teams], "Studio") then "Apple Data-St-Studio"
    else if Text.Contains([Checkout Teams], "Trading") then "Apple Banana-Prod-ITE-Trad-Trading"
    else if Text.Contains([Checkout Teams], "ABC-Prod Eng-Inv & Trad-Risk") then "Apple Banana-Prod-ITE-Trad-Trading"
    else if Text.Contains([Checkout Teams], "ABC-Proactive Engineering-AE-Investor Data") then "Apple Banana-Prod-AE-Investor Data"
    else if Text.Contains([Checkout Teams], "ABC-Proactive Engineering-AE-Model Eng") then "Apple Banana-Prod-AE-Pop Eng"
    else if Text.Contains([Checkout Teams], "ABC-Proactive Engineering-AE-Pop Eng") then "Apple Banana-Prod-AE-Pop Eng"
    else if Text.Contains([Checkout Teams], "ABC-Proactive Engineering-AE-Apple Research") then "Apple Banana-Prod-AE-Pop Eng"
    else if Text.StartWith([Checkout Teams], "ABC-Proactive Engineering-AG-") then "Apple Banana-Prod-Alpha & Digital Assets"
    else if Text.StartWith([Checkout Teams], "ABC-Proactive Engineering-Cash-") then "Apple Banana-Prod-ITE-Cash-Cachematrix"
    else if Text.Contains([Checkout Teams], "ENT") then "Apple Banana-Prod-ENT-Enterprise"
    else if Text.Contains([Checkout Teams], "Inv & Trad-FIX Conn") then "Apple Banana-Prod-ITE-Tra

 

0 REPLIES 0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.