cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Amar-Agnihotri
Helper I
Helper I

Want to merge data coming from 4 excel files in a single master file

Hello guys,
I need a suggestion. I have 4 excel files coming from different sources. All these files customer data. I want to combine that data to make a single master customer table in power query. What should be the best way to create a primary key column. I am thinking of generating a random number column and then concatenation it to the customer name. New column generated will be the primary key column. Is this a good one? Please give your opinion and of course if there can be a better option than this. Main problem is that customer column of all the files have different values and no common column to apply merge. I want to combine all sheets to make a master customer  data 
Thanks.

4 REPLIES 4
Amar-Agnihotri
Helper I
Helper I

@Greg_Deckler  2 sheets are integrated so that if I push customer in one sheet others gets updated but other 2 sheets may have different data of customers 

Hi @Amar-Agnihotri ,

Like @ Greg_Deckler  mentioned, if do not have the public column in these tables to merge, you can consider using an index column instead of a random column to get the better performance.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Spoiler
 

@Greg_Deckler your dax cookbook looks awesome. Will definitely go for that...👍

Greg_Deckler
Super User
Super User

@Amar-Agnihotri Well, your approach will work provided that each of the four files contains only unique customers. But, if you have the same customer in 2 or more files, this approach will essential create duplicates. So, first question, are customers in each of the 4 files truly unique? You might get better performance out of an Index column rather than a random number.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors