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"

     

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc7LCoMwEIXhVzm4rmix0G5FlyLFoF2Ii2kdSqg24gR8/d4QDKTr82X+tG1Qx/skgcrSPAVZFEUTKl54Drqdd7yQ9Yz1dJ+pZ/kqejCKZjWHBBX12qDiiT+PcY4yt+EnTslDzPtClJlxXM1xj0YTSnPVA6G2etBWs3hn9wPu5IRPMfJaQU10Y5TMvSAVYZGRn3aD/CCUbeY/W37J7gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t]),
    Removal = Table.TransformColumns(Source, {"Project", each let pos=Text.PositionOf(_, "-", Occurrence.First) in if pos>-1 then Text.ReplaceRange(_, pos, 10, "") else _})
in
    Removal