Forum Discussion
Post PatronHow 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.
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?
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
Hi Niels_T
Sorry, long day, doing code in my head ....
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Countries"}})Regards
Phil
14 Replies
darentengmfsPost Prodigy
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_THow 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.
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.
- PhilipTreacy
Super User
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
