empty cell

[sks2701]

Hi 

In my data set I have a column (country) that has many cells empty as in the information is not avaibale , howeevr there is another coulmn ( program ) in the data base through which i can identify the correct country , please can someone guide me the required step i need to take to achieve filling the empty cells in the country column - thank you!

[BA_Pete]

Hi @sks2701 ,

When you say you can identify the correct country via [Program], what do you mean? Does the [Program] field contain country names, or do you mean that you can use this as a join field?

It would be ideal if you could provide examples of your tables here. Please remove any sensitive information if you do.

Pete

[sks2701]

Yes sure so an exmaple would look like this , where in xxx is the program name with the country details in the end

program	Country
xxx Turkey	Turkey
xxx Australia	Austria
xxx Switzerland
xxx France

[BA_Pete]

Ok. So does the xxx in the [Program] field contain letters/numbers/spaces/dots? Anything that would help me to distinguish between the xxx portion of [Program] and the country portion?

For example, if the xxx portion is always a 4-digit number with no spaces then this would be quite easy to split out the country. If it's free-text, then not so easy.

Pete

[sks2701]

Yes the xxx are 7 to 8 words (it varies) separated by space , howeevr the last word will always be the country

[BA_Pete]

Ok. That makes it very difficult as some countires can be two, three, or more, words long themselves (think United States, Papua New Guinea etc.).

Are there any consistent characters between the program name and the country name that don't feature in the program name e.g. comma, apostrophe, hyphen etc.?

Pete

[sks2701]

No just space between the words - 

[BA_Pete · Accepted solution]

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