cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MiltonKeynes
New Member

Replacing missing values based on two columns

Hello everyone!

 

I'm having trouble preparing my data using Power Query in Power BI. I have a dataset that has the following columns: CustomerID, Street Name, Zip Code and Location Code. Location Code has some missing values, but I am able to fill in some of those by merging another query (customer info). Still, I am left with some missing values. In Excel I solved the problem by looking up street name and zip code, and returning corresponding location code (location code is based on this pair).

 

However, I am a literal noob in Power Query M and, even though I have given this a lot of thought, have not been able to solve the problem. Even though the number of missing values is not that significant, I would prefer not losing data in this case.

 

My data looks something like this:

CustomerIDStreet nameZip CodeLocation code
123Street 11235000
456Street 24565002
789Street 1123 
987Street 39875003
654Street 3987 

 

Obviously, the end result should look like this:

CustomerIDStreet nameZip CodeLocation code
123Street 11235000
456Street 24565002
789Street 11235000
987Street 39875003
654Street 39875003

 

I would appreciate any help and suggestions. I am not even sure if this is possible or practical in the first place.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Replacing missing values based on two columns

In Power Query:

Duplicate the query

Remove the Customer and Street Name columns.

Filter out the null entries in Location Code column (using the dropdown in the column heading)

This leaves you a kind of master table which matches Zip codes with Locations.

 

Using 'Merge Queries->Merge as New'

Merge the original and duplicated queries using an Inner Join on the Zip Code.

This gets you a table like this

 

MergeZips.PNG

Expand the column with 'Table' in each row (using the icon in the column heading)

You are really only interested in the new Location Code column. It should be fully populated.

Tidy up your data ( by Removing the old Location Code column)

View solution in original post

3 REPLIES 3
Super User I
Super User I

Re: Replacing missing values based on two columns

In Power Query:

Duplicate the query

Remove the Customer and Street Name columns.

Filter out the null entries in Location Code column (using the dropdown in the column heading)

This leaves you a kind of master table which matches Zip codes with Locations.

 

Using 'Merge Queries->Merge as New'

Merge the original and duplicated queries using an Inner Join on the Zip Code.

This gets you a table like this

 

MergeZips.PNG

Expand the column with 'Table' in each row (using the icon in the column heading)

You are really only interested in the new Location Code column. It should be fully populated.

Tidy up your data ( by Removing the old Location Code column)

View solution in original post

Highlighted
MiltonKeynes
New Member

Re: Replacing missing values based on two columns

I got it working, thank you very much!

 

Apologies for the elementary question.

Super User I
Super User I

Re: Replacing missing values based on two columns

Don't worry. It's not an elementary question

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors