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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ChrisR22
Helper II
Helper II

Creating a Master Name Index

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 NameClient Name (Table 1)Client Name (Table 2)Client Name (Table 3)
Apple Inc.AppleAPPLApple Incorporated
Amazon.comAmazonAMZNAmazon.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!

1 REPLY 1
amitchandak
Super User
Super User

@ChrisR22 , Try if Table.AddFuzzyClusterColumn of power query can help

Power Query Table.AddFuzzyClusterColumn: https://youtu.be/osF1z5kgvMo

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.