Forum Discussion
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:
| U0133 SCADA at LLV-Sewer |
| U0133 SCADA at LLV-Water |
| U0133 SCADA Upgrades at Lake LV |
| U0143 Radio Repeater P/C-Sewer |
| U0143 Radio Repeater P/C-Water |
| U0143 Radio Repeater Power/Comm |
| U0171 Via Nobila Utilities |
| U0171 Via Nobila-Sewer |
| U0171 Via Nobila-Water |
| U0180 DUS Space Needs Assessment |
| U0180 Space Needs Assessment-sewer |
| U0180 Space Needs Assessment-water |
I want the main project to be the primary name. So the -water and - sewer would go away. Since the names aren't all pretty I'm not sure how to go about replacing. Thoughts?
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"
7 Replies
You could split the column by delimiter (on -) ,if that holds as a rule
or
a replace -sewer (or whatever) with nothing .
Both options are a right-click on the column header in Power Query
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 RemovalYou 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"})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.
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 LVWhether 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-WaterWhat will name will it be U0171 Via Nobila or U0171 Via Nobila Utilities?
