Data merging and logic of it

[Sorin Bunea]

Hi guys,

I have a problem of both excel and good logic.

Need to merge different sources of data to get the correct details of businesses.
Each source has different data points about an entry. Each source of data has around 80% accuracy.

Things to consider:
Source 5 - "Activity" is less reliable than sources 2-4
Source 6 - "Activity" uses a different taxonomy and it's not necessarily helpful

I need to create a clean record of each company - so that there is only 1 location, 1 phone number, 1 website, 1 business activity.

I also need the logic behind it.. why a particular solution was chosen, why others were disregarded and, if the case, what other steps I would take take if I had resources to

I know this sound massive..

Thank you so much in advance!

[Glenn Kennedy]

Too much guesswork here. Please upload a sample that ALSO shows a few rows of what you want to see as the final result.

[Sorin Bunea]

You are right, I've added a new file.
Basically, on the first column let's say I have a company name and on the following 3 columns i have phone numbers from different sources, for that company. No source is 100% accurate, but I need to find out which one is more accurate and why.. This is just one company in the first row, I have dozens of them..

Thanks so much!

[sandy666]

Power Query

ON CA	85229071888	5
ON CA	4169749752	4
ON CA	18775957585	2
ON CA	16478043570	2
ON CA	14165650801	2
ON CA	18555122886	2
ON CA	17097003802	2
ON CA	14164568737	2
ON CA	8073441970	2
ON CA	18076235780	2
ON CA	12505626777	2
ON CA	16474783067	2
ON CA	14169793199	2
ON CA	15196527080	2
ON CA	19055992447	1
ON CA	19052432612	1
ON CA	16502530000	1
ON CA	12267839221	1
ON CA	6475307770	1
ON CA	6474787671	1
ON CA	18008842639	1
ON CA	4164568737	1
ON CA	6478043570	1
ON CA	8076235780	1
ON CA	16048060834,	1
ON CA	14186530488	1
ON CA	14168077405	1
ON CA	85228992286,	1
ON CA	16043401809	1
ON CA	18559929913	1
ON CA	19056079229	1
ON CA	9053028540	1
ON CA	15196819595	1
ON CA	14167609993	1
ON CA	5199885317	1
ON CA	18662367732	1
ON CA	19058140049	1
ON CA	16472819992	1
ON CA	9059511779	1
ON CA	9053033847	1
ON CA	9059536032	1

there are some duplicated Phones <> 1

or this layout

Count	Source 1 - full address	Phone
1	ON CA	18559929913 16043401809 9053028540 19056079229 85228992286, 16048060834, 8076235780 14168077405 14186530488 9059511779 16472819992 9059536032 9053033847 19058140049 14167609993 15196819595 18662367732 5199885317 12267839221 19055992447 16502530000 19052432612 4164568737 6478043570 6475307770 6474787671 18008842639
2	ON CA	12505626777 18775957585 18076235780 8073441970 17097003802 18555122886 14164568737 14165650801 16478043570 15196527080 14169793199 16474783067
4	ON CA	4169749752
5	ON CA	85229071888

also you can use Pivot Table

[Sorin Bunea]

Thanks so much..
But I think I don't get it.. Sorry

How can I actually find which of the 3 sources is more trust worthy (bigger accuracy)..

ps: my file has around 30K rows and I am no power query expert, unfortunately..

[sandy666]

is for you bigger accuracy = more phones ?

did you read about Pivot Table?

you didn't show expected result

maybe

Attribute	no data	Count
Source 1 - phone number	no data	17
Source 2 - Phone	no data	14
Source 3 - Phone		26
Source 3 - Phone	no data	7
Source 2 - Phone		19
Source 1 - phone number		16