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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

v-yiruan-msft

How to merge different rows with the same attribute into one row

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:

yingyinr_0-1620095885201.png

 

Expected result

yingyinr_1-1620095885204.png

 

Detailed steps

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

yingyinr_2-1620095885208.png

 

Then choose “OK”, and you will see:

yingyinr_3-1620095885208.png

 

2. Add a custom column:

yingyinr_4-1620095885211.png

 

Choose “OK” and you will see:

yingyinr_5-1620095885211.png

 

3. Modify the custom column expression as below:

yingyinr_6-1620095885213.png

 

And you will see:

 yingyinr_7-1620095885214.png

 

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

yingyinr_8-1620095885215.png

 

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

yingyinr_9-1620095885216.png

And you will see:

yingyinr_10-1620095885217.png

 

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

yingyinr_11-1620095885221.png

yingyinr_12-1620095885225.png

 

We will get an expected result as below:

 yingyinr_13-1620095885237.png

 

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

yingyinr_14-1620095885239.png

 

Author: Kelly Yang 

Reviewer: Ula Huang, Kerry Wang

Comments