Forum Discussion

Post Patron

5 years ago

Solved

How to fill blanks in column through Power Query

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. ...

Need Help

PhilipTreacy
5 years ago
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
5 years ago
Hi Niels_T

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

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

Regards

Phil

PhilipTreacy

Super User

5 years ago

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

Post Patron

5 years ago

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

Super User

5 years ago

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

Post Patron

5 years ago

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
Super User
5 years ago
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

Forum Discussion

How to fill blanks in column through Power Query

Related content

Remove Blank Columns

Blank Page - Embedded Report

Blanks in Conditional Column

Conditional column with blank

Blank columns when doing Measure Name Measure Value approach