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

[TJHughes]

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

[OwenAuger · Accepted solution]

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?