empty cell

Hi sks2701 ,

This solution requires you to have a table in Power Query that contains all the different countries that could appear in the [Program] text. If you have a [Country] dimension table in your data store, that would work perfectly.

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my example code blocks over the default code. You can then view the working solution and follow the steps I took to complete this.

Here is my sample country table for this example. Call this 'countryTable':

// Call this table 'countryTable'
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCiktyk6tVIrViVYKzcssSU1R8M7MS0/JzwULBSQWlCYq+KWWK7iXZualJiKrCy5JLEktBot4liTmAM2IBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [country = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"country", type text}})
in
    #"Changed Type"

Here is my sample program table with the country segment of the program name applied to a new column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKTy9KzFVIVAgpLcpOrVSK1UEIJmETTFYIzcssSU1R8M7MS0/Jz0WRTFEISCwoTVTwSy1XcC/NzEtNRJFOhekNLkksSS1GkUtT8CxJzEG1Kx2P+gwsVsUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [program = _t]),
    addFindCountry = Table.AddColumn(Source, "findCountry", each List.Transform(countryTable[country], (x) => Text.Contains([program], x))),
    addContainsCountry = Table.AddColumn(addFindCountry, "containsCountry", each List.AnyTrue([findCountry])),
    addCountry = Table.AddColumn(addContainsCountry, "Country", each try countryTable{List.PositionOf([findCountry], true)} [country] otherwise null, type text),
    remCols = Table.RemoveColumns(addCountry,{"findCountry", "containsCountry"})
in
    remCols

This gives me the following output:

Credit to ImkeF for this method on this thread.

Pete