Replace empty text values by most frequent value based in another column

[jessicarocha]

Hi everyone, 

there are master data problem with my dataset. I need to make a flow map and several customers have empty city location. However, there is the country available. 

Would it be possible to create a formula that replaces empty cells with the most frequent city in the dimension table for that country? When there is only one city per country, to choose at random?
 
In alternative, it could also be replaced by the Capital of that country, but I think that would be more difficult to achieve. 

Example of my dataset: 

Customer ID		Customer Nr.		Customer Name		Country		Country Code		City
1		000259		Cust A		Germany		DE		Cologne
2		059953		Cust B		Germany		DE		Munich
3		003697		Cust C		Germany		DE
4		789756		Cust D		Portugal		PT		Lisbon
5		698438		Cust E		Portugal		PT		Porto
6		659712		Cust F		Portugal		PT
7		579825		Cust G		Germany		DE		Munich

Desired output:

Customer ID		Customer Nr.		Customer Name		Country		Country Code		City
1		000259		Cust A		Germany		DE		Cologne
2		059953		Cust B		Germany		DE		Munich
3		003697		Cust C		Germany		DE		Munich
4		789756		Cust D		Portugal		PT		Lisbon
5		698438		Cust E		Portugal		PT		Porto
6		659712		Cust F		Portugal		PT		Lisbon
7		579825		Cust G		Germany		DE		Munich

I would be very grateful if you could help me. 

At the moment I am doing with replace values in Power Query, but that is a lot of manual work...

Thanks a lot in advance!

Kind regards, 

Jéssica

[amitchandak · Accepted solution]

@jessicarocha , In power query, fill down in an easy option , if that can work. The top city needs a summarized table then the top record filter and then merge and create a new column

[jessicarocha]

Hi @amitchandak , 

thanks for the help. The fill down option don't really work for me, but I will use your suggestion and create another table with the given cities. Then, I'll merge it. Thanks a lot!