cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Niels_T
Helper IV
Helper IV

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.

 

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? 

2 ACCEPTED SOLUTIONS

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Hi @Niels_T 

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

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

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

14 REPLIES 14
PhilipTreacy
Super User III
Super User III

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

start-c.png

 

and giving this result

result-c.png

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.

image.png

 

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

 

image.png

 

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

But it doesn't work then.

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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:

image.png

 

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:

image.png

 

Hope this can help to find a solution.

 

Best regards,

 

Niels

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

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:image.png

 

 

    #"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"

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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?

Hi @Niels_T 

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

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

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

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
Community Support
Community Support

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

image.png

 

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

darentengmfs
Super User I
Super User I

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

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors