Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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"
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"