none
Match Specific strings RRS feed

  • Question

  • Hi: I am trying to perform match for specific string/word inside column "Name" against the list of Locations called "Remote". Once match found, the location name to be returned in the custom column. Also, trying to achieve the same for Dates as per marked below. If no dates in the Name column then leave blank. Any assistance would be greatly appreciated. Thanks.

    https://1drv.ms/x/s!Amc8fiGpDxekhxivBhufTe1SCmIg

    Output

    Thursday, December 12, 2019 1:13 PM

Answers

  • Hi M.Awal

    You can make it work with List.Accumulate (more precise) but this might be slower - not sure - as you can't exit the loop once a {Remote} item is found in [Name], and if you have a long list…

    Just tested it on your workbook:

    let
        Expenses = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Remote = Table3[Short Name],
        AddedExpenseLocation = Table.AddColumn(Expenses, "Location", each
            List.Accumulate(Remote, null,
                (state, current)=> if Text.Contains([Name], current, Comparer.OrdinalIgnoreCase)
                                   then Text.Combine({state,current},", ")
                                   else state
            )
        )
    in
        AddedExpenseLocation



    • Marked as answer by M.Awal Friday, December 13, 2019 4:16 AM
    • Edited by Lz._ Saturday, December 28, 2019 5:54 PM
    Friday, December 13, 2019 3:08 AM
  • Hi again

    Did the following purely as a List.Generate learning exercise:

    let
        Expense = #table(type table [Name=Text.Type],{{"hello ABC and friends of jkl"},{"hello DEF and ghi"},{"helloAbCdEfGhIjKl"},{"hello world"}}),
        Remote = List.Buffer({"abc","def","ghi","jkl"}),
        AddedLocation = Table.AddColumn(Expense, "Location", (state)=>
            let
                genList = List.Generate(()=> [i=0, out=if Text.Contains(state[Name],Remote{i},Comparer.OrdinalIgnoreCase) then Remote{i} else null],
                                each [i] < List.Count(Remote),
                                each
                                    [
                                        i = [i]+1,
                                        current = Remote{i},
                                        out = if Text.Contains(state[Name],current,Comparer.OrdinalIgnoreCase)
                                              then current else null
                                    ],
                                each [out]
                            ),
                removeNulls = List.RemoveNulls(genList)
            in
                if List.IsEmpty(removeNulls) then null else Text.Combine(removeNulls,", "),
            Text.Type
        )
    in
        AddedLocation

    • Marked as answer by M.Awal Friday, December 13, 2019 11:24 PM
    Friday, December 13, 2019 9:16 AM
  • I like the way you incorporated List.Gen inside Table.AddCol.

    Note that I made a little adjustment to the code to exit List.Generate as there's no reason to do a Text.Combine on a single value => If List.IsEmpty(removeNulls) then null else List.First(removeNulls)

    IMHO this is the kind of thing that would better sit as an external function, i.e.

    fnFindFirstMatchingItem

    (string as any, itemList as list) as any =>
    let
        nbListItems = List.Count(itemList),
        genList = List.Generate(()=> [i=0, out=if Text.Contains(string,itemList{i},Comparer.OrdinalIgnoreCase) then itemList{i} else null],
                        each [i] < nbListItems,
                        each
                            [
                                i = if [out] <> null then [i]+nbListItems else [i]+1,
                                current = itemList{i},
                                out = if Text.Contains(string,current,Comparer.OrdinalIgnoreCase)
                                      then current else null
                            ],
                        each [out]
                    ),
        removeNulls = List.RemoveNulls(genList)
    in
        if List.IsEmpty(removeNulls) then null else List.First(removeNulls)

    Query code

    let
        Expense = #table(type table [Name=Text.Type],{{"hello AAC and friends of jkl"},{"hello DEF and ghi"},{"helloAaCddfGhIjKl"},{"hello world"}}),
        Remote = List.Buffer({"abc","def","ghi","jkl"}),
        AddedLocation = Table.AddColumn(Expense, "Location",
            (state)=> fnFindFirstMatchingItem(state[Name],Remote),
            Text.Type
        )
    in
        AddedLocation

    Hope this makes sense & helps. BTW you Marked as answer your screenshot, not sure this will help others ;-)


    • Edited by Lz._ Friday, December 13, 2019 11:13 AM
    • Marked as answer by M.Awal Friday, December 13, 2019 11:25 PM
    Friday, December 13, 2019 11:11 AM
  • Just for the record, with List.Transform (you can't exit the loop in that case):

    let
        Expense = #table(type table [Name=Text.Type],{{"hello AAC and friends of jkl"},{"hello DEF and ghi"},{"helloAaCddfGhIjKl"},{"hello world"}}),
        Remote = List.Buffer({"abc","def","ghi","jkl"}),
        AddedLocation = Table.AddColumn(Expense, "Location", (i)=>
            let
                genList = List.Transform(Remote, each
                    if Text.Contains(i[Name], _,Comparer.OrdinalIgnoreCase)
                    then _ else null
                ),
                removeNulls = List.RemoveNulls(genList)
            in
                 if List.IsEmpty(removeNulls) then null else Text.Combine(removeNulls,", "),
            Text.Type
        )
    in
        AddedLocation

    • Marked as answer by M.Awal Friday, December 13, 2019 11:22 PM
    Friday, December 13, 2019 1:55 PM
  • Hi

    Assuming your version of Excel/Power Query has function Table.FuzzyNestedJoin

    1/ Change Remote query (BTW it's not required - you could use Table3) with:

    let
        Source = Table.SelectColumns(Table3, {"Short Name"})
    in
        Source

    2/ Expense query code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        DuplicatedColumn = Table.DuplicateColumn(Source, "Name", "NameCopy"),
        ReplacedValue = Table.ReplaceValue(DuplicatedColumn," - "," ",Replacer.ReplaceText,{"NameCopy"}),
        ReplacedValue1 = Table.ReplaceValue(ReplacedValue,"-"," ",Replacer.ReplaceText,{"NameCopy"}),
        FuzzyJoin = Table.FuzzyNestedJoin(ReplacedValue1,"NameCopy", Remote,"Short Name", "Remote", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=false, Threshold=0.1]),
        ExpandedLocation = Table.ExpandTableColumn(FuzzyJoin, "Remote", {"Short Name"}, {"Location"}),
        RemovedNameCopy = Table.RemoveColumns(ExpandedLocation,{"NameCopy"})
    in
        RemovedNameCopy


    Output (incomplete):

    If this solves your problem please Mark as answer (can help others…) Thanks

    EDIT corresponding workbook avail. here
    • Edited by Lz._ Thursday, December 12, 2019 5:20 PM added sample
    • Marked as answer by M.Awal Friday, December 13, 2019 12:51 AM
    Thursday, December 12, 2019 5:16 PM
  • And to exit the loop on 1st matching item:

    let
        Expense = #table(type table [Name=Text.Type],{{"hello AAC and friends of jkl"},{"hello DEF and ghi"},{"helloAaCddfGhIjKl"},{"hello world"}}),
        Remote = List.Buffer({"abc","def","ghi","jkl"}),
        NbRemoteItems = List.Count(Remote),
        AddedLocation = Table.AddColumn(Expense, "Location", (state)=>
            let
                genList = List.Generate(()=> [i=0, out=if Text.Contains(state[Name],Remote{i},Comparer.OrdinalIgnoreCase) then Remote{i} else null],
                                each [i] < NbRemoteItems,
                                each
                                    [
                                        i = if [out] <> null then [i]+NbRemoteItems else [i]+1,
                                        current = Remote{i},
                                        out = if Text.Contains(state[Name],current,Comparer.OrdinalIgnoreCase)
                                              then current else null
                                    ],
                                each [out]
                            ),
                removeNulls = List.RemoveNulls(genList)
            in
                if List.IsEmpty(removeNulls) then null else List.First(removeNulls),
            Text.Type
        )
    in
        AddedLocation


    • Edited by Lz._ Friday, December 13, 2019 10:43 AM Little code adjust.
    • Marked as answer by M.Awal Friday, December 13, 2019 11:24 PM
    Friday, December 13, 2019 9:28 AM

All replies

  • Hi

    Assuming your version of Excel/Power Query has function Table.FuzzyNestedJoin

    1/ Change Remote query (BTW it's not required - you could use Table3) with:

    let
        Source = Table.SelectColumns(Table3, {"Short Name"})
    in
        Source

    2/ Expense query code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        DuplicatedColumn = Table.DuplicateColumn(Source, "Name", "NameCopy"),
        ReplacedValue = Table.ReplaceValue(DuplicatedColumn," - "," ",Replacer.ReplaceText,{"NameCopy"}),
        ReplacedValue1 = Table.ReplaceValue(ReplacedValue,"-"," ",Replacer.ReplaceText,{"NameCopy"}),
        FuzzyJoin = Table.FuzzyNestedJoin(ReplacedValue1,"NameCopy", Remote,"Short Name", "Remote", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=false, Threshold=0.1]),
        ExpandedLocation = Table.ExpandTableColumn(FuzzyJoin, "Remote", {"Short Name"}, {"Location"}),
        RemovedNameCopy = Table.RemoveColumns(ExpandedLocation,{"NameCopy"})
    in
        RemovedNameCopy


    Output (incomplete):

    If this solves your problem please Mark as answer (can help others…) Thanks

    EDIT corresponding workbook avail. here
    • Edited by Lz._ Thursday, December 12, 2019 5:20 PM added sample
    • Marked as answer by M.Awal Friday, December 13, 2019 12:51 AM
    Thursday, December 12, 2019 5:16 PM
  • Hi Lz: Thanks for the solution. I haven't used the Fuzzy Nested Join yet. Good opportunity to learn about it. Anyway, my Excel version doesn't have Fuzzy Nested Join, I tried it in Power Bi and it works.

    The reason I used Remote list because initially, I tried to come up with the solution using List.Accumulate as per follows. It didn't work for some reason. 

    List.Accumulate(Remote, {}, (state, current) => if Text.Contains([NAME], current) then List.Combine({{current}, state}) else state)

    Friday, December 13, 2019 12:51 AM
  • Hi M.Awal

    You can make it work with List.Accumulate (more precise) but this might be slower - not sure - as you can't exit the loop once a {Remote} item is found in [Name], and if you have a long list…

    Just tested it on your workbook:

    let
        Expenses = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Remote = Table3[Short Name],
        AddedExpenseLocation = Table.AddColumn(Expenses, "Location", each
            List.Accumulate(Remote, null,
                (state, current)=> if Text.Contains([Name], current, Comparer.OrdinalIgnoreCase)
                                   then Text.Combine({state,current},", ")
                                   else state
            )
        )
    in
        AddedExpenseLocation



    • Marked as answer by M.Awal Friday, December 13, 2019 4:16 AM
    • Edited by Lz._ Saturday, December 28, 2019 5:54 PM
    Friday, December 13, 2019 3:08 AM
  • Thanks. I like this one as well.
    Friday, December 13, 2019 4:16 AM
  • You're welcome, glad I could help

    BTW, if you decide to go with something like List.Accumulate it's probably a good idea to buffer your Remote list

    • Edited by Lz._ Friday, December 13, 2019 6:03 AM
    Friday, December 13, 2019 5:13 AM
  • Hi again

    Did the following purely as a List.Generate learning exercise:

    let
        Expense = #table(type table [Name=Text.Type],{{"hello ABC and friends of jkl"},{"hello DEF and ghi"},{"helloAbCdEfGhIjKl"},{"hello world"}}),
        Remote = List.Buffer({"abc","def","ghi","jkl"}),
        AddedLocation = Table.AddColumn(Expense, "Location", (state)=>
            let
                genList = List.Generate(()=> [i=0, out=if Text.Contains(state[Name],Remote{i},Comparer.OrdinalIgnoreCase) then Remote{i} else null],
                                each [i] < List.Count(Remote),
                                each
                                    [
                                        i = [i]+1,
                                        current = Remote{i},
                                        out = if Text.Contains(state[Name],current,Comparer.OrdinalIgnoreCase)
                                              then current else null
                                    ],
                                each [out]
                            ),
                removeNulls = List.RemoveNulls(genList)
            in
                if List.IsEmpty(removeNulls) then null else Text.Combine(removeNulls,", "),
            Text.Type
        )
    in
        AddedLocation

    • Marked as answer by M.Awal Friday, December 13, 2019 11:24 PM
    Friday, December 13, 2019 9:16 AM
  • And to exit the loop on 1st matching item:

    let
        Expense = #table(type table [Name=Text.Type],{{"hello AAC and friends of jkl"},{"hello DEF and ghi"},{"helloAaCddfGhIjKl"},{"hello world"}}),
        Remote = List.Buffer({"abc","def","ghi","jkl"}),
        NbRemoteItems = List.Count(Remote),
        AddedLocation = Table.AddColumn(Expense, "Location", (state)=>
            let
                genList = List.Generate(()=> [i=0, out=if Text.Contains(state[Name],Remote{i},Comparer.OrdinalIgnoreCase) then Remote{i} else null],
                                each [i] < NbRemoteItems,
                                each
                                    [
                                        i = if [out] <> null then [i]+NbRemoteItems else [i]+1,
                                        current = Remote{i},
                                        out = if Text.Contains(state[Name],current,Comparer.OrdinalIgnoreCase)
                                              then current else null
                                    ],
                                each [out]
                            ),
                removeNulls = List.RemoveNulls(genList)
            in
                if List.IsEmpty(removeNulls) then null else List.First(removeNulls),
            Text.Type
        )
    in
        AddedLocation


    • Edited by Lz._ Friday, December 13, 2019 10:43 AM Little code adjust.
    • Marked as answer by M.Awal Friday, December 13, 2019 11:24 PM
    Friday, December 13, 2019 9:28 AM
  • This is one deserves a top kudos. Does this {i} referring to list? 

    • Marked as answer by M.Awal Friday, December 13, 2019 9:39 AM
    • Unmarked as answer by M.Awal Friday, December 13, 2019 11:24 PM
    Friday, December 13, 2019 9:39 AM
  • Does this {i} referring to list? 

    Given you earlier said you were playing with List.Accumulate I assumed - apologies - you would understand that one

    YES. Remote{i}: 1st iteration (i=0) so this takes List Remote item with index 0; 2nd iteration (i=1) so this takes List Remote item with index 1 ...

    Hope this explains otherwise let me know

    (Didn't understand - in your picture - why you highlighted View native query. If this has nothing to do with the above question forget about it otherwise explain)

    Friday, December 13, 2019 9:58 AM
  • Thanks for the explanation. Make sense. I like the way you incorporated List.Gen inside Table.AddCol. I wasn't sure whether it is query folding or not when I saw the cog symbol next to it.
    Friday, December 13, 2019 10:05 AM
  • I like the way you incorporated List.Gen inside Table.AddCol.

    Note that I made a little adjustment to the code to exit List.Generate as there's no reason to do a Text.Combine on a single value => If List.IsEmpty(removeNulls) then null else List.First(removeNulls)

    IMHO this is the kind of thing that would better sit as an external function, i.e.

    fnFindFirstMatchingItem

    (string as any, itemList as list) as any =>
    let
        nbListItems = List.Count(itemList),
        genList = List.Generate(()=> [i=0, out=if Text.Contains(string,itemList{i},Comparer.OrdinalIgnoreCase) then itemList{i} else null],
                        each [i] < nbListItems,
                        each
                            [
                                i = if [out] <> null then [i]+nbListItems else [i]+1,
                                current = itemList{i},
                                out = if Text.Contains(string,current,Comparer.OrdinalIgnoreCase)
                                      then current else null
                            ],
                        each [out]
                    ),
        removeNulls = List.RemoveNulls(genList)
    in
        if List.IsEmpty(removeNulls) then null else List.First(removeNulls)

    Query code

    let
        Expense = #table(type table [Name=Text.Type],{{"hello AAC and friends of jkl"},{"hello DEF and ghi"},{"helloAaCddfGhIjKl"},{"hello world"}}),
        Remote = List.Buffer({"abc","def","ghi","jkl"}),
        AddedLocation = Table.AddColumn(Expense, "Location",
            (state)=> fnFindFirstMatchingItem(state[Name],Remote),
            Text.Type
        )
    in
        AddedLocation

    Hope this makes sense & helps. BTW you Marked as answer your screenshot, not sure this will help others ;-)


    • Edited by Lz._ Friday, December 13, 2019 11:13 AM
    • Marked as answer by M.Awal Friday, December 13, 2019 11:25 PM
    Friday, December 13, 2019 11:11 AM
  • Just for the record, with List.Transform (you can't exit the loop in that case):

    let
        Expense = #table(type table [Name=Text.Type],{{"hello AAC and friends of jkl"},{"hello DEF and ghi"},{"helloAaCddfGhIjKl"},{"hello world"}}),
        Remote = List.Buffer({"abc","def","ghi","jkl"}),
        AddedLocation = Table.AddColumn(Expense, "Location", (i)=>
            let
                genList = List.Transform(Remote, each
                    if Text.Contains(i[Name], _,Comparer.OrdinalIgnoreCase)
                    then _ else null
                ),
                removeNulls = List.RemoveNulls(genList)
            in
                 if List.IsEmpty(removeNulls) then null else Text.Combine(removeNulls,", "),
            Text.Type
        )
    in
        AddedLocation

    • Marked as answer by M.Awal Friday, December 13, 2019 11:22 PM
    Friday, December 13, 2019 1:55 PM
  • Hi LZ: it looks like each time you take the solution to the next level. I also like this approach.
    Friday, December 13, 2019 11:22 PM
  • Another master stroke from you with your manipulation of List.Gen. Sorry mistakenly hitting my screen shot as answer.
    Friday, December 13, 2019 11:27 PM
  • Just having fun - as a learning exercise - with the standard functions that allow looping. Glad I could help
    Saturday, December 14, 2019 11:15 AM
  • Also, trying to achieve the same for Dates as per marked below. If no dates in the Name column then leave blank.

    So is the above no longer part of your requirement?

    Sunday, December 15, 2019 4:47 AM
  • Thanks for following up Colin. I will try work it out from one of the codes that Lz_ provided above. I guess it would be same principle. 
    Sunday, December 15, 2019 4:55 AM
  • Hi Colin:

    I tried to separate date from the text string. Because there is no pattern it is very hard.

    Would it be possible to separate the date from the Text String as per the first screen shot?

    Thanks

    Friday, December 20, 2019 5:59 AM