Forum Discussion

irishguurl's avatar
irishguurl
New Member
4 years ago
Solved

Bulk replace in M language

I have a table that has project names and numbers in seperate columns.  I have grouped the columns together but I have multiple instances of the same project and with different names.  For example: ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Here is the solution based on Fuzzy matching with a sensitivity of 0.5. Download example file from https://1drv.ms/x/s!Akd5y6ruJhvhuTWHGs0sOgEVhYHd?e=3nFBvL 

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Data], "-")),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Data", each Text.BeforeDelimiter(_, "-"), type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Extracted Text Before Delimiter"),
    #"Merged Queries" = Table.FuzzyNestedJoin(Source, {"Data"}, #"Removed Duplicates", {"Data"}, "Removed Duplicates", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, NumberOfMatches=1, Threshold=0.5]),
    #"Expanded Removed Duplicates" = Table.ExpandTableColumn(#"Merged Queries", "Removed Duplicates", {"Data"}, {"Removed Duplicates.Data"})
in
    #"Expanded Removed Duplicates"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

You can use following in a custom column and then delete the original column

try Text.Start([Project],Text.PositionOf([Project],"-")) otherwise [Project]

For column inplace replace, use following code (change Source with your last step)

Table.ReplaceValue(Source,each [Project], each try Text.Start([Project],Text.PositionOf([Project],"-")) otherwise [Project],    Replacer.ReplaceValue,{"Project"})

 

  • irishguurl's avatar
    irishguurl
    New Member
    4 years ago

    I believe I can replace the -water and -sewer part through the replace option in the tool bar.  What complicates things is the base name of the project differs. See example below:

     

    U0143 Radio Repeater P/C-Sewer

    U0143 Radio Repeater P/C-Water

    U0143 Radio Repeater Power/Comm

     

    Ideally, I would want U0143 Radio Repeater Power/Comm to be the project name for all 3 versions.  Does that make more sense?  This issue occurs for many projects.

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Either we need a list of replacement words or some convention to identify...For example -

      U0133 SCADA at LLV-Sewer
      U0133 SCADA at LLV-Water
      U0133 SCADA Upgrades at Lake LV

      Whether name will be U0133 SCADA Upgrades at Lake LV or U0133 SCADA at LLV
      U0171 Via Nobila Utilities
      U0171 Via Nobila-Sewer
      U0171 Via Nobila-Water

      What will name will it be U0171 Via Nobila or U0171 Via Nobila Utilities?

      • irishguurl's avatar
        irishguurl
        New Member
        4 years ago

        I think it would be easier to replace with whatever is before the -water or -sewer.  So in the case of the examples you show, it would be U0133 SCADA at LLV and U0171 Via Nobila.