Use a list in another tables add column formula in Power Query

TJHughes — Sat, 15 Mar 2025 05:37:28 GMT

I have column I have added in Power Query which works as I need it to. It searches a list of post codes and checks if column in the table contains that post code and gives Y/N answer. The issue is that the list is hardcoded and not dynamic. I do have another table in the report that does have the list which is dynamic but I dont know if I can change my current formula to use this list instead?

Here is current formula I am using:

= Table.AddColumn(#"Changed Type", "Custom", each if( List.AnyTrue( List.Transform( { "B1 1","B1 2","B1 3","B10 0","B10 9","B11 1","B11 2","B11 3","B11 4","B11 9","B12 0","B12 8","B12 9","B13 0","B13 3","B13 8","B13 9","B14 4","B14 5","B14 6","B14 7","B15 1","B15 2","B15 3","B16 0","B16 6","B16 8","B16 9","B17 0","B17 8","B17 9","B18 4","B18 5","B18 6","B18 7","B19 1","B19 2","B19 3" }, (sunstring) => Text.Contains( [postcode], sunstring ) ) ) ) = true then "Y" else "N")

Re: Use a list in another tables add column formula in Power Query

OwenAuger — Sat, 15 Mar 2025 08:57:23 GMT

Hi TJHughes

Yes, you can replace the current list expression {...} with a reference to the dynamic list.

For example, if the list is made up of the values in a column of a table, say Table[Column], you could write this (I slightly edited the code):

= Table.AddColumn( #"Changed Type", "Custom", each if List.AnyTrue(List.Transform(Table[Column], (sunstring) => Text.Contains([postcode], sunstring))) then "Y" else "N" )

Otherwise change Table[Column] to an appropriate reference to the dynamic list.

Does this work for you?

topic Re: Use a list in another tables add column formula in Power Query in DAX Commands and Tips

Use a list in another tables add column formula in Power Query

Re: Use a list in another tables add column formula in Power Query