Transform Cell into Rows

[Stocki-San]

Scenario:

Current Table:

ID  Date  Topic

1   1.1.18 X;Y,Z

2   2.1.18 X;Z

Transform to:

ID  Date  Topic

1   1.1.18 X

1   1.1.18 Y

1   1.1.18 Z

2   2.1.18 X

2   2.1.18 Z

Any Ideas? Other Solutions in the Forum didn't help.

[MarcelBeug · Accepted solution]

2 Ideas.

1. Choose some transform operations on the transform columns menu, e.g. "Trim"(in the "Format" group) and adjust the generated code.

let
    Source = CurrentTable,
    #"Splitted Text" = Table.TransformColumns(Source,{{"Topic", each Text.Split(_,";"), type {text}}}),
    #"Expanded Topic" = Table.ExpandListColumn(#"Splitted Text", "Topic")
in
    #"Expanded Topic"

2. Use split column with advanced option Split into rows. Resulting code (no adjustment required):

let
    Source = CurrentTable,
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Topic", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Topic"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Topic", type text}})
in
    #"Changed Type"

[p0nk]

First split the Topic column by delimiter, in this case a semicolon.Repeat this step on the second columkn that has been created but this time for a comma.Select the Topic columns that have been created and press unpivot columns.Remove the attribute column that was created from the unpivot and you have the end result.