Forum Discussion

UnearthlyFalcon's avatar
UnearthlyFalcon
Advocate I
7 months ago
Solved

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

 

CountryCity
USParis
GermanyBerlin
CanadaToronto
FranceParis
SpainMadrid
MexicoMexico City
ItalyRome
USBerlin
GermanyBerlin
CanadaToronto
FranceParis
SpainMadrid
MexicoMexico City
ItalyRome

US

Madrid
GermanyBer
CanadaTor

 

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!!

4 Replies

  • Why would Paris be incorrect for US? There are literally hundreds of towns in the US called Paris.

  • Hi UnearthlyFalcon,

    Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to lbendlinMFelix,  for those inputs on this thread.

    Has your issue been resolved? If the response provided by the community member lbendlinMFelix,  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.

    • UnearthlyFalcon's avatar
      UnearthlyFalcon
      Advocate 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.