Forum Discussion

AsiaY's avatar
AsiaY
Copper Contributor
Jun 21, 2019

Text Filter

Hello All,    Would you be able to tell me how to filter two columns of individual names for differences, that rule out middle initials?    I've tried using a filter that would tell me if the nam...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 22, 2019

For the collection, Power Query variant

by

let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    RemoveMiddle = Table.AddColumn(Source, "Different Names", each
        let
            splitNames = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Names])
        in
            Text.Combine({splitNames{0}, " ", List.Last(splitNames)}),
    type text),
    RemoveDuplicates = Table.Distinct(RemoveMiddle, {"Different Names"}),
    RemoveSource = Table.SelectColumns(RemoveDuplicates,{"Different Names"})
in
    RemoveSource

Actually no coding is required, script is generated by Column By Example.

AsiaY's avatar
AsiaY
Copper Contributor
Jun 24, 2019

SergeiBaklan 

 

Hello Sergei, 

 

Your example is great except I am needing to only find names that are different in two columns of names, where each name is listed side by side but could be listed different as Calendar Amy L or Calendar Amy Lynn … 

 

Any suggestions? 

Resources