locked
Smith John M.: How to Get Rid of "M." No Matter On Which Position It Is? RRS feed

  • Question

  • Hi guys,

    data set:

    1000s of names in one column. Some values associated to the names in another column.

    I use the names as key for matching with data from another query.

    The problem is that the names are sometimes in wrong order or they contain middle name abbreviations in one query and not in another query. 

    Typically, I would get "Smith John M." in one query and "John Smith" in another query. So I need to normalize the names first. I was thinking to use List Functions for that.

    I need to: 1) Order the words within the name (that's easy via List.Sort) & 2) disregard all the strings shorter then 2 characters

    Currently I have this formula for a custom column: List.Select(Text.Split([Key Base]," "), each Text.Length>2) where "Key Base" is the name column.

    I am struggling to pull only those strings which are longer than 2 characters via the List.Select function. Any tips on how to achieve that?

    Thx!

     

    Saturday, May 14, 2016 9:30 AM

Answers

  • You need to pass on a parameter to your Text.Length(_)

    So it will be this:

    List.Select(Text.Split([Key Base]," "), each Text.Length(_)>2


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Daniel Zrust Saturday, May 14, 2016 1:09 PM
    Saturday, May 14, 2016 12:59 PM

All replies

  • You need to pass on a parameter to your Text.Length(_)

    So it will be this:

    List.Select(Text.Split([Key Base]," "), each Text.Length(_)>2


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Daniel Zrust Saturday, May 14, 2016 1:09 PM
    Saturday, May 14, 2016 12:59 PM
  • Awesome! I knew I had wrong something with my syntax :)

    DZ

    Saturday, May 14, 2016 1:09 PM