Forum Discussion
Split column by delimiter at multiple positions
- 4 years ago
Try splitting on ";" into rows rather than columns.
Now add a couple of index columns starting from zero then mod one column by 4 and integer divide the other column by 4 to get this:
Now you can pivot on the [i] column using [Original] as the values column. The result should look like this:
From here, you can rename, remove, or combine columns as desired.
Full sample query you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZKxTsMwEIZf5WSJLUQmbaHIUyMELF1gYAgZ4uTUWnXvkO2oYuV5eCqeBDuhQrRSBSy2JZ+/7/6Tq0pciEyUtqF2jdChh3smtBa9uszlGZxD6Rrv0Zl0C/a5l7K4WmjdvI7lD9z6DEq0K9Nv1c4EbdA9rU1A9fH2PsnnUtRZJYpoOXgVyT2huv6HJu0It+xCWqlFLB3vKBmnuZTqyGSZOoyJZn9W6eEp6GQczmo6BJrEQEs2hCH28MWfH/Jv+hem8LNv0xA8Bse0gju23cjcN/5NHGbzK+CpQSyZNmNAH6XcBzXLi9NIn8rih2g3e4qo608=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref = _t, Original = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Original", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Original"), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Ref", Int64.Type}, {"Original", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "i", 0, 1, Int64.Type), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "j", 0, 1, Int64.Type), #"Calculated Modulo" = Table.TransformColumns(#"Added Index1", {{"i", each Number.Mod(_, 4), type number}}), #"Integer-Divided Column" = Table.TransformColumns(#"Calculated Modulo", {{"j", each Number.IntegerDivide(_, 4), Int64.Type}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Integer-Divided Column", {{"i", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Integer-Divided Column", {{"i", type text}}, "en-US")[i]), "i", "Original") in #"Pivoted Column" - 4 years ago
NewStep=#table(Table.ColumnNames(PreviousStepName),List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(Text.Split(_{1},";"),4),(x,y)=>{x{0},Text.Combine(y,";")}))
NewStep=#table(Table.ColumnNames(PreviousStepName),List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(Text.Split(_{1},";"),4),(x,y)=>{x{0},Text.Combine(y,";")}))
Thank you as well, it works!