How to fill blanks in column through Power Query

[Niels_T]

Hello,

I have a column "Countries" and a column "telephone". In the "countries" column I have blank data but, I want to fill this up through Power Query with the help of the "telephone" column.

I thought of using each telephone number in the "telephone" column that starts with for example "+44", that this will add "Great Britian" to the blank spot and this for all the other countries.

Is this possible? 

[PhilipTreacy · Accepted solution]

All good @Niels_T 

I've modified the code slightly to take into account the true format of the phone numbers (text) we're dealing with.  Replace the code from (and including) the #"Added Custom2" step with this

    #"Added Custom2" = Table.AddColumn(#"Replaced Value23", "Custom", each if Text.Contains(Text.Start([telephone],3), "+44") or Text.Contains(Text.Start([telephone],4), "0044") then "Great Britain" 

        else if Text.Contains(Text.Start([telephone], 4) , "0032") then "Belgium" 

        else if Text.Contains(Text.Start([telephone], 3), "001") then "United States" 

        else if Text.Contains(Text.Start([telephone], 4) , "0061") then "Australia" 

        else null),

    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Countries"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Countries"}})
in
    #"Renamed Columns2"

Regarding any blanks, I've written code to take account of UK and Belgian numbers, and added a coupleof extra else if statements in to illustrate how to modify the code for other countries.

else if Text.Contains(Text.Start([telephone], 3), "001") then "United States" 

You can see that the code is checking the first 3 characters of the phone number (using the Text.Start function) to see if it matches (Text.Contains) the string "001".  If it does then that phone number is from the United States.

For Australian numbers the code has to check the first 4 characters for "0061".

Hopefulyl that makes it clear how to add code for other countries but if you get stuckjust post back.

regards

Phil

[PhilipTreacy]

Hi @Niels_T 

Download this sample PBIX with code.

If you have data as shown then the phone numbers must be stored as text in order to be able to check for leading zeroes. So, the following Power Query code will do what you want.  This is easily expanded to include more countries.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jYxqQABJR0lpVidaCUDA4gAgm9shIUP5Dql5qRnluai63IvSk0sUXAqyixJzMxTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [telephone = _t, countries = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([telephone], "+44") or Text.Contains([telephone], "0044") then "Great Britain" else if Text.Contains([telephone], "0032") then "Belgium" else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"countries"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "countries"}})
in
    #"Renamed Columns"

Starting with this

and giving this result

Regards

Phil

[Niels_T]

I'm trying to use your method on my real data, but it doesn't work.

Here is a screenshot of my data. As you can see at the top it contains blanks which I want to fill through the telephone codes.

This is what is in my advanced editor. I used Power Query before to filter and change values

I tried to add bellow my last line in let the code you provided me

But it doesn't work then.

[PhilipTreacy]

Hi @Niels_T 

When providing data please either copy/paste it or provide a file.  Screenshots are good to see what you have but you can't work with a screenshot, you have to type the data out manually.

You said you tried to add my code but it didn't work.  How so?  Did you get an error?

Your screenshot doesn't show where you added my code.

In your image of the data, every row in the Countries column is populated - where are the blanks you refer to?

To add my code to yours, first add a comma to the end of your last step #"Replaced Value23" then delete everything after that and replace it with this

#"Added Custom2" = Table.AddColumn(Source, "Custom", each if Text.Contains([telephone], "+44") or Text.Contains([telephone], "0044") then "Great Britain" else if Text.Contains([telephone], "0032") then "Belgium" else null),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"countries"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "countries"}})
in
    #"Renamed Columns2"

If you are still having issues please provide your Power Query code and sample data - delete the last few characters of each phone number to preserve privacy.

Regards

Phil

[Niels_T]

Hi Phil,

Thank you so far for the support. I wish  Icould provide sample data but I'm still a beginner and I don't know immediately how to do this.

Here is the code:

let
    Source = MySQL.Database("xxx.xx.xxx.xx", "xxxxxxx", [ReturnSingleDatabase=true]),
    xxxxxxxxxxxxxxx = Source{[Schema="xxxxxxxxxxxxx",Item="sales_flat_order_address"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(xxxxxxxxxxxxxxxxxxxxx,{{"country_id", "Countries"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"vat_id", "vat_is_valid", "vat_request_id", "vat_request_date", "vat_request_success", "prefix", "middlename", "suffix", "company", "quote_address_id", "fax"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Total Customers", each 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([address_type] = "billing") and ([email] <> "xxxxxxxxxxxxx" and [email] <> "xxxxxxxxxxxxxxxxx" and [email] <> "xxxxxxxxxxxxxxxx" and [email] <> "xxxxxxxxxxxxxxxx" and [email] <> "xxxxxxxxxxxxxxxxxxxxx" and [email] <> "xxxxxxxxxxxxxxxxxxxxxxxxxx")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"telephone"}),
    #"Removed Duplicates1" = Table.Distinct(#"Removed Duplicates", {"email"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates1","BE","Belgium",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","DE","Germany",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","NL","Netherlands",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","FR","France",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","LU","Luxembourg",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","AT","Austria",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","CH","Switzerland",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","CZ","Czech Republic",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","DK","",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","EE","",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","ES","",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","FI","",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","FR","France",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","GB","",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","GR","",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","IE","",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","IT","Italy",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","LT","",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","LU","Luxembourg",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","NL","Netherlands",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","PL","Poland",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","SE","Sweden",Replacer.ReplaceText,{"Countries"}),
    #"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","SK","Slovakia",Replacer.ReplaceText,{"Countries"}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value22", each true),
    #"Replaced Value23" = Table.ReplaceValue(#"Filtered Rows1","MT","Malta",Replacer.ReplaceText,{"Countries"}),
    #"Added Custom2" = Table.AddColumn(Source, "Custom", each if Text.Contains([telephone], "+44") or Text.Contains([telephone], "0044") then "Great Britain" else if Text.Contains([telephone], "0032") then "Belgium" else null),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Countries"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Countries"}})
in
    #"Renamed Columns2"

The error message I received is as followed:

This is strange because my column is called Countries.

And yes you might not see the blanks immediately but they are definetly in the table as you can see here:

Hope this can help to find a solution.

Best regards,

Niels

[PhilipTreacy · Accepted solution]

All good @Niels_T 

I've modified the code slightly to take into account the true format of the phone numbers (text) we're dealing with.  Replace the code from (and including) the #"Added Custom2" step with this

    #"Added Custom2" = Table.AddColumn(#"Replaced Value23", "Custom", each if Text.Contains(Text.Start([telephone],3), "+44") or Text.Contains(Text.Start([telephone],4), "0044") then "Great Britain" 

        else if Text.Contains(Text.Start([telephone], 4) , "0032") then "Belgium" 

        else if Text.Contains(Text.Start([telephone], 3), "001") then "United States" 

        else if Text.Contains(Text.Start([telephone], 4) , "0061") then "Australia" 

        else null),

    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Countries"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Countries"}})
in
    #"Renamed Columns2"

Regarding any blanks, I've written code to take account of UK and Belgian numbers, and added a coupleof extra else if statements in to illustrate how to modify the code for other countries.

else if Text.Contains(Text.Start([telephone], 3), "001") then "United States" 

You can see that the code is checking the first 3 characters of the phone number (using the Text.Start function) to see if it matches (Text.Contains) the string "001".  If it does then that phone number is from the United States.

For Australian numbers the code has to check the first 4 characters for "0061".

Hopefulyl that makes it clear how to add code for other countries but if you get stuckjust post back.

regards

Phil

[Niels_T]

I added the code under mine.

I'm still doing something wrong but I don't know what because I get now this error message:

    #"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","SK","Slovakia",Replacer.ReplaceText,{"Countries"}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value22", each true),
    #"Replaced Value23" = Table.ReplaceValue(#"Filtered Rows1","MT","Malta",Replacer.ReplaceText,{"Countries"}),
    #"Added Custom2" = Table.AddColumn(#"Replaced Value23", "Custom", each if Text.Contains(Text.Start([telephone],3), "+44") or Text.Contains(Text.Start([telephone],4), "0044") then "Great Britain" 

        else if Text.Contains(Text.Start([telephone], 4) , "0032") then "Belgium" 

        else if Text.Contains(Text.Start([telephone], 3), "001") then "United States" 

        else if Text.Contains(Text.Start([telephone], 4) , "0061") then "Australia" 

        else null),

    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Countries"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Countries"}})
in
    #"Renamed Columns2"

[PhilipTreacy]

Sorry @Niels_T that's my fault.  There's a typo in my code. It's hard when I don't actually have the data in front of me.

Replace the #"Renamed Columns2" line with this

#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Countries"}})

Regards

Phil

[Niels_T]

Hello @PhilipTreacy  , 

I still get the same error message that custom cannot be found. 

#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Countries"}})

I think the line is exactly the same as the line that I should replace?

[PhilipTreacy · Accepted solution]

Hi @Niels_T 

Sorry, long day, doing code in my head ....

#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Countries"}})

Regards

Phil

[Niels_T]

Thank you so much @PhilipTreacy !!!

It is working! Now I just have to make sure that I add all the phonecodes and then everything is perfect!

Again thank you for your help really appreciate it!

[v-yuaj-msft]

Hi @Niels_T ,

Can you share some sample data and the expected result to have a clear understanding of your question? 

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

Best Regards,

Yuna

[Niels_T]

I don't know how to make some sample data but this screenshot might explain a bit what I would like to achieve.

[darentengmfs]

@Niels_T 

You will need to have a table that has phone codes that ties to each country. So your table will have 2 columns - Phone Code and Country.

Then, you can extract the phone code from your telephone column in your first table, then merge the first table and the new table using the phone codes, then expand the country column and use that as your new country column.

[Niels_T]

How can I make a new table with these phone code columns and countries? 

I tried with DAX but I can't seem to get the result I wanted.