M code List.cotntains and text combine does not work

Power Query
1

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

image
image427×516 6.82 KB

image
image1416×351 11.7 KB

Could please advise what I have don’t incorrect?

2

@Matty This works?

image
image1338×324 12.8 KB

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"
4

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)

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

I hope this is helpful.

2 Likes