Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jessicarocha
Helper IV
Helper IV

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

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.