Forum Discussion
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: ...
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"
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.
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"