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 Group | Implementer Teams | Checkout Teams |
| ABC-Pop-HS-Bouillabaise HosTimeng | ABC-Pop-HS-Bouillabaise HosTimeng, Alts, Managed Flowers | ABC-Prod Eng-Inv & Trad-Risk, Managed Flowers, ABC-Pop-HS-Bouillabaise HosTimeng |
| Managed Flowers | Alts-banana, Bouillabaise Hostimeng, Managed Flowers, | Managed Flowers, ABC-Prod-ENT |
After hunners of replacements
| Change Group | Implementer Teams | Checkout Teams |
| Alpha Beta-Pop-HS-Bouillabaise HosTimeng | Alpha Beta-Pop-HS-Bouillabaise HosTimeng, Alpha Beta-Pop-MC-Alts, Alpha Beta-Pop-MC-Protime Ops | Alpha Beta-Pop-PO-Product Office, Alpha Beta-Pop-MC-Protime Ops, Alpha Beta-Pop-HS-Bouillabaise HosTimeng |
| Alpha Beta-Pop-MC-Protime Ops | Alpha Beta-Pop-MC-Alts-Flipper, Alpha Beta-Pop-MC-Protime Ops | Alpha 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
