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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amien
Helper IV
Helper IV

Split column not into seperate columns but in rows

how is this possible? 

 

For example:

 

id, Column
1,A;B;C

should become:

 

id, Column
1, A
1, B
1, C
1 ACCEPTED SOLUTION
Anonymous
Not applicable

 

Hi amien,

 

   to solve your problem step by step, you have to:

 

1) Select the column

8.png

 

2) Split columns by delimiter (comma)

9.png

 

3) Do the transpose (columns become rows & rows become columns)

10.png

 

4) Split columns by delimiter (semicolon)

11.png

 

5) Replace the null values with the chosen values

12.png

 

6) Redo the transpose

 

13.png

( . . . )

Let me know if it works.

 

Have a good coding

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

How to do the just reverse of this operation.I mean from the shown output to input format.

MACKnox
Advocate I
Advocate I

It's been some time now, and, columns can be split directly into rows with one step:

 

Column2Row.PNG

Hi,

 

I am attempting to use this option. The window that pops up looks exactly like your image, but the option for columns or rows in the middle is just missing. The original choices from Get & Transform only allowed From Table. There was no From Table/Range option. Any direction you might provide would be so helpful.Capture1.JPG

Capture 2.JPG

 

Anonymous
Not applicable

 

Hi amien,

 

   to solve your problem step by step, you have to:

 

1) Select the column

8.png

 

2) Split columns by delimiter (comma)

9.png

 

3) Do the transpose (columns become rows & rows become columns)

10.png

 

4) Split columns by delimiter (semicolon)

11.png

 

5) Replace the null values with the chosen values

12.png

 

6) Redo the transpose

 

13.png

( . . . )

Let me know if it works.

 

Have a good coding

 

What If I want to split all columns by delimeter at once ?

Also would like to have the null value showing the correct value.

Should I do that manually by advanced editor ?

WillT
Community Admin
Community Admin

You could use Split by Delimiter and then Unpivot the resulting columns to get to this. There's details on Unpivot here: https://support.office.com/en-us/article/Unpivot-columns-Power-Query-0f7bad4b-9ea1-49c1-9d95-f588221... Hope that helps!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.