none
Search For Keywords in Text String RRS feed

  • Question

  • Hello,

    I've recently begun using Power Query to generate a large report. However, I've run into problem where an array formula checks for the first partial keyword in another table. I've been searching for a Power Query solution in M, but nothing seems to replicate similar behaviour to the following Excel formula:

    {=IFERROR(INDEX(Table2[Keywords],MATCH(1,COUNTIF(Table1[Path],"*"&Table2[Keywords]&"*"),0)),"")}

    Any suggestions would be appreciated!

    Thanks,

    Darren


    MSDN Thread update Cheers, Darren

    Tuesday, January 15, 2019 3:08 AM

Answers

  • Can be simplified a wee bit, as there is no need to test if the matching tables are empty. Also, ignoring case is better than changing the source data.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        AddedColumn =
            Table.AddColumn(
                Source,
                "Match",
                (i)=> Table.SelectRows(
                          Table2,
                          (j) => Text.Contains(i[Path], j[Keywords], Comparer.OrdinalIgnoreCase)
                      )
            ),
        FirstRows = Table.TransformColumns(AddedColumn, {"Match", each Table.First(_)}),
        ExpandedMatch = Table.ExpandRecordColumn(FirstRows, "Match", {"Keywords", "Description"})
    in
        ExpandedMatch

    Tuesday, January 15, 2019 4:15 PM

All replies

  • Hi Darren

    With the following scenario your formula returns abd (COUNTIF returned array: {2,1,4,0}) while - I suspect - you would expect ZZZ


    Assuming ZZZ is the expected result

    let
        PathList = List.Buffer(
            List.Transform(Table1[Path], each Text.Lower(_))
        ),
        KeywordList = List.Buffer(
            List.RemoveNulls(Table2[Keywords])
        ),
        MatchingList = List.RemoveNulls(
            List.Transform(KeywordList, each
                if List.IsEmpty(List.FindText(PathList, Text.Lower(_)))
                then null else _
            ) 
        ),
        Result = if List.IsEmpty(MatchingList) then null
                 else MatchingList{0}  
    in
        Result

    Not sure the above will survive a long time here as there might be a more efficient solution…

    On Excel side: {=IFERROR(INDEX(Table2[Keywords],MATCH(1,SIGN(COUNTIF(Table1[Path],"*"&Table2[Keywords]&"*")),0)),"")}

    Tuesday, January 15, 2019 8:29 AM
  • Hi Lz,

    This seems to be working, but I apologize for not including the expected result. When I was using in Excel, the keywords were sorted in descending order to force a kind of prioritization. (The more text, the higher the probability the partial match is correct.)

    I include all the records from Table 1 with the partially matched keywords and description from Table 2. This way, I will know where the matches occurred. Non-matched items are also important as I may need to take an action with them.

    Table 1
    Path Add'lData
    abc 1a
    abcdef 2b
    eeezzzabc 3c
    wwwzzzyyy 4d

    Table 2
    Keywords Length Description
    zzzabc 6 Text 1
    abcd 4 Text 2
    ZZZ 3 Text 3

    Result
    Path Add'lData Keyword Description
    abc 1a
    abcdef 2b abcd Text 2
    eeezzzabc 3c zzzabc Text   1
    wwwzzzyyy 4d zzz Text 3

    Thanks,

    Darren


    MSDN Thread update Cheers, Darren

    Tuesday, January 15, 2019 1:59 PM
  • Darren

    Could you check (works for me based on your sample) the following

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        TypeChanged = Table.TransformColumnTypes(Source,{{"Path", type text}, {"Add'lData", type text}}),
        #"Added Custom" = Table.AddColumn(TypeChanged, "FirstMatchingKeyword",
            (i) =>
                let
                    select = Table.SelectRows(Table2, each Text.Contains(Text.Lower(i[Path]), Text.Lower([Keywords]))),
                    result = if Table.RowCount(select) > 1
                             then Table.FirstN(select, 1)
                             else select
                in
                    result,
            type table
        ),
        ExpandedFirstMatchingKeyword = Table.ExpandTableColumn(#"Added Custom", "FirstMatchingKeyword",
            {"Keywords", "Description"}, {"Keywords", "Description"})
    in
        ExpandedFirstMatchingKeyword

    • Proposed as answer by Colin Banfield Tuesday, January 15, 2019 4:08 PM
    Tuesday, January 15, 2019 3:05 PM
  • Can be simplified a wee bit, as there is no need to test if the matching tables are empty. Also, ignoring case is better than changing the source data.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        AddedColumn =
            Table.AddColumn(
                Source,
                "Match",
                (i)=> Table.SelectRows(
                          Table2,
                          (j) => Text.Contains(i[Path], j[Keywords], Comparer.OrdinalIgnoreCase)
                      )
            ),
        FirstRows = Table.TransformColumns(AddedColumn, {"Match", each Table.First(_)}),
        ExpandedMatch = Table.ExpandRecordColumn(FirstRows, "Match", {"Keywords", "Description"})
    in
        ExpandedMatch

    Tuesday, January 15, 2019 4:15 PM
  • Can be simplified a wee bit, as there is no need to test if the matching tables are empty. Also, ignoring case is better than changing the source data.

    Hi Colin

    I was about to update my proposal with Comparer.OrdinalIgnoreCase for the reason you mention + I suspect it's more direct/efficient than Lowering each string

    Thanks for FirstRows = Table.TransformColumns(AddedColumn, {"Match", each Table.First(_)}). I wrongly assumed Table.First or Table.FirstN would fail/return an error with an empty table  

    Tuesday, January 15, 2019 4:37 PM
  • Hi Darren

    Just saw the exact same requirement (Text.StartsWith that time) on the regular Excel forum. If this thread had been Marked as Answer... 

    Sunday, January 20, 2019 8:46 AM