Move everything after comma to new row

Bine · Oct 1, 2021

Dear all,

I have a list of lots of genes and unfortunately sometimes I have more than one gene in one row e.g.:

ENSG00000063978, ENSG00000125386

ENSG00000248323

ENSG00000137261

ENSG00000235220, ENSG00000204642, ENSG00000137403, ENSG00000237508, ENSG00000229698, ENSG00000206509

ENSG00000183826

ENSG00000135298

ENSG00000185345

ENSG00000106066

ENSG00000283239, ENSG00000274726, ENSG00000104728

ENSG00000153707

But I want that every gene is in a separate row without comma, like this (first 4 lines of above example):

ENSG00000063978
ENSG00000125386

ENSG00000248323

ENSG00000137261

Has anyone an idea?

Thank you so much!!

p45cal · Oct 2, 2021

This is straightforward in Power Query, see attached. Right-click and refresh the green table.
This is the M code in that file:

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.TransformColumns(Source,{{"Jeans", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}),
    ExpandedJeans = Table.ExpandListColumn(Split, "Jeans")
in
    ExpandedJeans

Move everything after comma to new row

Bine

New member

p45cal

Super Moderator

Attachments