How to match different name of city and hotel from different files?

[teddy2511]

Dear all,

I have two files (with many rows) which actually refer to the same hotel and same city. They are shown under (a little bit) different names. I want to combine spend from two files.

I was trying to create the third file, which gives unique names for hotels and cities, which I want to see. Then I linked the two files to it. But after creating a relationship there is always an error message that it needs to have an unique value.

I've heard about geo-located data (using the address), which can be used to identify the same hotels. But don't know if it is possible in Power BI or how to do it.

So, how can I solve this problem?

e.g.

File 1

City: Frankfurt

Hotel: Ibis hotel

Spend: 1234€

File 2

City: Frankfurt am Main

Hotel: Ibis Centre hotel

Spend: 456€

Thank you very much!

[v-yuta-msft]

Hi teddy2511,

In your senario, you can import your files then click query editor-> append queries or you can put all your files  in a folder and then import that folder then you can combine all your files together.

Regards,

Jimmy Tao

[teddy2511]

Thanks for the reply.

If I append the files (3-4 files with each more than 10.000 rows), then I will have a lot of data rows. And the different names are still be shown for the same hotel.

[dedelman_clng]

40,000 rows is really not that many for a BI tool.  40,000,000 might be a different story.

Unfortunately, there are many times that data quality issues that cannot be solved by the tool you are using.  Sounds like this data needs to be cleaned up at the source (and likely manually).

Options

- Comb through the data and append a unique ID to each hotel and one for each city. Merge the queries together in PowerBI and use either MIN or MAX of the names based on the ID to perform your analysis (for each ID, display MIN(hotel name), MIN(city name), sum(spend))

- Get a geolocation tag from an outside source for the hotel and do the same as above (outside of PowerBI). The added benefit here is that you can use a map visual if you wanted to

Wish there was a way to have PowerBI do data cleanup, but as far as I know there isn't.

David