cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nohasalem
Frequent Visitor

Split multiple columns into rows at once

I have a table that has 37 columns. each column needs to be split in to rows. If I did that to one by one it is a big mess. I need a code to apply to make it all at once. Here is a sample of the table

nohasalem_0-1618088996416.png

I need to see it as

index    Programming languages    Integrated development environments etc..

1          python                                   jupyter

1           null                                        Rstudio

10        R                                            Rstudio

10        SQL                                        Vm/Emacs

10        Juilia                                      null

10        Bash                                        null

 

I have tried this code:

= List.Accumulate(
Table.ColumnNames(Source),
Source,
(st, cur) =>
if List.AnyTrue(List.Transform(Table.Column(st, cur), each Text.Contains(_, ";")))
then Table.SplitColumn(st, cur, Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), 6)
else st
)

But it split them in to columns not rows 

nohasalem_1-1618089260678.png

I need help as soon as possible 

1 ACCEPTED SOLUTION
Rocco_sprmnt21
Super User II
Super User II

Based on the proposed example, this script should do what is required.
An implicit assumption, for example, is that indexes are unique.
If something does not work as desired, explain well what by loading the tables (so that they can be copied, not as images) on which it does not work and indicating exactly the error and where it occurs.

View solution in original post

2 REPLIES 2
Rocco_sprmnt21
Super User II
Super User II

Based on the proposed example, this script should do what is required.
An implicit assumption, for example, is that indexes are unique.
If something does not work as desired, explain well what by loading the tables (so that they can be copied, not as images) on which it does not work and indicating exactly the error and where it occurs.

View solution in original post

This is amazing! it worked like a charm.

I had to replace nulls first to get it working

= Table.TransformColumns(Source,{},(x) => Replacer.ReplaceValue(x,null,""))

but overall it worked out very well

Thank you very much

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors