M code List.cotntains and text combine does not work

Dear DNA Team,

@Melissa already helped me two weeks ago. I added an additional statement to the m-code but it does not work as it supposes to:
List filters.xlsx (165.9 KB)

current m code:

= Table.SelectRows(#“Changed Type”, each (not List.Contains(ExcludeList, [Acct Code]) and ((List.Contains(IncludeList, [Reference])) or Text.Contains(":Reversal",[Reference]))))

I have added or Text.Contains(":Reversal",[Reference]) to include Reversal in reference column but the data does not give me the correct result


Could please advise what I have don’t incorrect?

@Matty This works?

let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
            Source, 
            {{"Acct Code", type text}, {"Reference", type text}}
        ),
    #"Filtered Rows" = Table.SelectRows(
            #"Changed Type", 
            each (not List.Contains(ExcludeList, [Acct Code])
                and ((List.Contains(IncludeList, [Reference]))
                or Text.Contains([Reference], ":Reversal")
            )
            )
        )
in
    #"Filtered Rows"

Hi @Matty,

I 've listed the syntax for both functions below and also included links to the documentation.

Text.Contains( text as nullable text, substring as text, optional comparer as nullable function)

https://docs.microsoft.com/en-us/powerquery-m/text-contains

List.Contains( list as list, value as any, optional equationCriteria as any)

https://docs.microsoft.com/en-us/powerquery-m/list-contains

I hope this is helpful.

2 Likes