Hello,
I have multiple data sources that each contain a client name column. I would like to create a master list with all client names that I can later use as a slicer to search all of the datasets. Here is my situation:
a. there is no singular data sheet that has all of the client names, nor is there a common index between the sources. each source has their own index ID system
b. there are many duplicates, both within a single sheet and between sheets
c. some sheets format the names quite differently than the others (Apple vs. APPL vs. Apple Inc., etc.), preventing the system from identifying the match between tables
Does anyone have ideas on how to make a singluar sheet summarizing our client list?
My initial thought would be to create a table with all the aliases:
| Client Name | Client Name (Table 1) | Client Name (Table 2) | Client Name (Table 3) |
| Apple Inc. | Apple | APPL | Apple Incorporated |
| Amazon.com | Amazon | AMZN | Amazon.com, Inc. |
The only issue is how to create this, either within power bi or in excel? Short of manually searching each table for the alias for each of the hundreds of clients we have, I cannot think of a way to do it. I would also like the solution to be formula driven within Power BI so that when clients are added and removed, the system would update this Client Alias Table automatically, instead of me having to be aware of each addition/subtraction and making the edit in the table by hand.
If anyone has experience with this situation please let me know. Help is greatly appreciated!
