Correct similar rows of a table

[mtrevisiol]

Hi everyone!

I've got a table with the list of customers, it is something like this

CustSupp	CompanyName	City	Address	Zip Code	Telephone	Country Code
01022	**POLAND SRL	Warsawa	Red Street, 32		+48 123	PL
01562	POLAND S.R.L.	Warsawa	Red Street	05092	+48 123

These two customers are the same company, so I'd like to have only one row that represent it in my table. The problem is that some information are contained in the first row (the street number, the country code) and others in the second one (the zip code).

What is the best way to modify this data using power query, in order to have all the data available in a single line, like

CustSupp	CompanyName	City	Address	Zip Code	Telephone	Country Code
01022	POLAND S.R.L.	Warsawa	Red Street, 32	05092	+48 123	PL

 ?

Thank you so much.

[v-kelly-msft · Accepted solution]

Hi @mtrevisiol ,

You could use "group by":

Then reorder the column per your request,and you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mtrevisiol]

@v-kelly-msft  thank you!

[v-kelly-msft]

Hi  @mtrevisiol ,

Is there any other column that can be used as the key value to determine that the companies are the same one.

For example,can Telephone be used as a unique value to distinguish companies?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mtrevisiol]

@v-kelly-msft Yes, I think that telephone can be used as a key.

[Fowmy]

@mtrevisiol 

You need at least one column that has a common value in both the rows, I see ** in Company Name.
And, the address column is not consistent. Can you share a bigger sample that represents your actual data, you can repalce confidential information. 

[mtrevisiol]

@Fowmy why did you mention the ** in the company name? I don't know why they have been inserted.