How To Pass a List through Filter in Power Query Editor?

[Anonymous]

Hi,

I've got a list of agent numbers in one list, e.g. 0001, 0002, 0003, 0004.

Basically, I want to (in Advanced Editor/Query Editor) use an IF statement to force an output if the agent number matches one of those in that list.

The current formula is written as:

if Text.Contains([Division], "SINGAPORE")
    and [QTR Flag] = "Y"
    and [QMR Flag] = "Y"
    and [Issuing Carrier]="QF"
    and not Text.StartsWith([Reservation Class], "G")
    and not Text.StartsWith([Reservation Class], "N")
    and not Text.StartsWith([Reservation Class], "O")
    and not Text.StartsWith([Reservation Class], "Q")
    and Table.SelectRows(AppendedMARS, each List.Contains(IncentivisedAgents, [IncentivisedAgents]))
    then "SG"

I want to add, something like this:

and ([Agent ID] = "0001"
or [Agent ID] = "0002"
or [Agent ID] = "0003"
or [Agent ID] = "0004")

but I don't want to manually hardcode this in.

I have imported a table which includes all the Agent IDs that I wish to include. The name of the table is "IncentivisedAgents" and the name of the column within which the Agent IDs are stored is also called "IncentivisedAgents". "AppendedMARS" refers to the table name within which this query is being written.

I've therefore tried to modify the code to be as follows:

if Text.Contains([Division], "SINGAPORE")
    and [QTR Flag] = "Y"
    and [QMR Flag]="Y"
    and [Issuing Carrier]="QF"
    and not Text.StartsWith([Reservation Class], "G")
    and not Text.StartsWith([Reservation Class], "N")
    and not Text.StartsWith([Reservation Class], "O")
    and not Text.StartsWith([Reservation Class], "Q")
    and Table.SelectRows(AppendedMARS, each List.Contains(IncentivisedAgents, [IncentivisedAgents]))
    then "SG"

I learnt the "Table.SelectRows" function method from this post.

However, this is now giving me an error, "Expression.Error: A cylic reference was encountered during evaluation". If it helps, the "IncentivisedAgents" column is stored as text, but it looks like a number. I've stored it as text because some of the values have leading 0s, and storing it as a number tends to omit the 0s.

Would appreciate any help. Thanks.

[Mariusz · Accepted solution]

Hi @Anonymous 

Please see the attached file with a solution

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Thanks so much! For the benefit of others, the format ends up being as follows:

List.Contains( IncentivisedAgents[Agent ID], [Agent ID] )

Where "IncentivisedAgents" is the name of the definer table with the list, the first "[Agent ID]" is the name of the column within the definer table, and the second "[Agent ID]" is the name of the column within your current table/query (or fact table) containing the agent numbers you want looked up.

The "Table.SelectRows" that I was trying to incorporate is actually a filter function within the Query Editor that physically filters out entries/rows that don't match, which is not what I was trying to achieve.