Forum Discussion
Solved
Split column to rows issue
Hi there, I thought it would be a simple matter but I have not found a solution despite a few hours spent on this. I have a table that looks like the one below, just with many more rows and...
You can try something like the following.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRIzEtRcFLSUTJUitVB5huB+c5AljGY5QJkmYBZrkCWKZgVAVYdCeSbofHNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Value = _t]), #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"AllRows", each _[Value], type table [Key=nullable text, Value=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NewKey", each [Key], type text), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"NewKey", Splitter.SplitTextByDelimiter(" and ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "NewKey"), #"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Position", each [AllRows]{List.PositionOf(Text.Split([Key], " and "), [NewKey])}, type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Key", "AllRows"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewKey", "Key"}, {"Position", "Value"}}) in #"Renamed Columns"From:
To:
You can try something like the following.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRIzEtRcFLSUTJUitVB5huB+c5AljGY5QJkmYBZrkCWKZgVAVYdCeSbofHNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Value = _t]),
#"Grouped Rows" = Table.Group(Source, {"Key"}, {{"AllRows", each _[Value], type table [Key=nullable text, Value=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "NewKey", each [Key], type text),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"NewKey", Splitter.SplitTextByDelimiter(" and ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "NewKey"),
#"Added Custom1" = Table.AddColumn(#"Split Column by Delimiter", "Position", each [AllRows]{List.PositionOf(Text.Split([Key], " and "), [NewKey])}, type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Key", "AllRows"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewKey", "Key"}, {"Position", "Value"}})
in
#"Renamed Columns"From:
To: