cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amien Member
Member

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

Accepted Solutions
B129 Member
Member

Re: Split column not into seperate columns but in rows

 

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

 

5 REPLIES 5
Moderator WillT
Moderator

Re: Split column not into seperate columns but in rows

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!
B129 Member
Member

Re: Split column not into seperate columns but in rows

 

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

 

Highlighted
suoko Frequent Visitor
Frequent Visitor

Re: Split column not into seperate columns but in rows

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 ?

MACKnox Visitor
Visitor

Re: Split column not into seperate columns but in rows

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

 

Column2Row.PNG

Anonymous
Not applicable

Re: Split column not into seperate columns but in rows

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 51 members 1,167 guests
Please welcome our newest community members: