How to merge different rows with the same attribut…

Scenario: 

In many cases, the data we get is entered in rows, which may seem redundant. How to merge these rows under the same attribute into one row in the form of columns?

In actual tables, there may be multiple rows, but here I’ll create a simple table as an example. The main table is like below:

 

Expected result：

 

Detailed steps：

1. Go to power query>select Hospital No.>Group by:

 

Then choose “OK”, and you will see:

 

2. Add a custom column:

 

Choose “OK” and you will see:

 

3. Modify the custom column expression as below:

 

And you will see:

 

 

4. Move the mouse on the right side of the custom column and click “Extract Value”:

 

 5. Choose any one of Delimiter to extract values, here we will choose “Comma” for example:

And you will see:

 

6. Go to Home> Split Columns>By delimiter:

 

We will get an expected result as below:

 

 

Finally, remove the columns we don’t need,and we will get what we want:

 

Author: Kelly Yang 

Reviewer: Ula Huang, Kerry Wang