Forum Discussion
Combine/ Integrate/ Replace text in one column from multiple?
Hi all,
I have a bit of a mess and I'm not even sure how to explan, so thanks just for reading, though any suggestions are much appreciated!
I can't share real data, so I'll try to explain best I can with example data.
I have Table1 below, where there are columns for country and city and other omitted columns, however each US entry has an incorrect city (I only want Paris to amtch with France and Berlin with Germany). There are duplicates but I need to retain all information in Table1 (the other columns have unique data in each row).
| Country | City |
| US | Paris |
| Germany | Berlin |
| Canada | Toronto |
| France | Paris |
| Spain | Madrid |
| Mexico | Mexico City |
| Italy | Rome |
| US | Berlin |
| Germany | Berlin |
| Canada | Toronto |
| France | Paris |
| Spain | Madrid |
| Mexico | Mexico City |
| Italy | Rome |
US | Madrid |
| Germany | Ber |
| Canada | Tor |
The City column also has some blanks and cityies that won't match, so I can't do this for the larger data set. I need to filter out the bad actors first.
I created a reference table to Table1 'US' in Power Query, then fltered for US, since that is where the incorrect cities are. Then I have Table2 that has a list of common world cities with their respective countries where I have tried two things
1. Create new column using LOOKUPVALUE to return the correct world country for the 'US' table from Table2 [WorldCountries]
2. Created a relationship between US [City] and Table2 [WorldCities] so that Table2 [World Countries] can work with US [City].
Now where I am stuck: Is there a way to integrate the Table2 [World Countries] and the original Table1 [Country] into one column so that i can use this one correct column?
I hope that makes sense. Table1[Country] has 80% correct data, I just need to correct the other 20%. Any semi-simple way to do this?
Also, if it matters, in my real data I have 2 bad actors I need to correct and was planning on just repeating this process for the second.
Thank you!!
Hi UnearthlyFalcon ,
What you need to do is on Power Query to merge the two tables this will allow to combine the two table into one and then use the column you need.
4 Replies
- MFelixSuper User
Hi UnearthlyFalcon ,
What you need to do is on Power Query to merge the two tables this will allow to combine the two table into one and then use the column you need.
- lbendlinSuper User
Why would Paris be incorrect for US? There are literally hundreds of towns in the US called Paris.
- v-kpoloju-msftCommunity Support
Hi UnearthlyFalcon,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to lbendlin, MFelix, for those inputs on this thread.Has your issue been resolved? If the response provided by the community member lbendlin, MFelix, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.- UnearthlyFalconAdvocate I
v-kpoloju-msft No, neither answer worked for my situation, unfortunately. However, I have marked MFelix's response as solution becasue for a normal situation it would work.