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? 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 23, 2019

    SergeiBaklan 

    I think you are correct in that PQ is the way to go with data analysis problems.  In particular I welcome the degree of structure offered by Tables and PQ that is missing from normal spreadsheet usage.

     

    Now though, DAs have hugely improved the usability of arrays for model building.  What is confusing is that the new DA functions also provide an alternative approach for data analysis steps (sorting, filtering etc). 

     

    There is now a big area of overlap where either methodology is viable.  I am not certain where the borderlines are in terms of which option should be recommended for what problems.  At the moment, it is a case of trying each and determining on a case-by-case basis which works out the better.

Resources