Forum Discussion
Split Two Columns into Rows by their Respective Delimiters.
I need to spilt two columns into rows e.g Heading7 and Heading9. I could do it individually using excel formulas but I need the adjacent rows to be inserted and repeated as shown in the output screenshot. Grey highlight are the repeated rows.
Input
Output
Sample Data
| Heading1 | Heading2 | Heading3 | Heading4 | Heading5 | Heading6 | Heading7 | Heading8 | Heading9 | Heading10 |
| 12345666 | Demo text1 | Some text1 | 5545567 | RTN | Electric | 5(q)-I,5(n)-I,5(o)-II,5(e),9(w),9(e)-I,9(e)-II | Sometext2 | 21-Apr-2023; 21-Sep-2025; 21-Sep-2025; 21-Sep-2025; 21-Sep-2025; 21-Sep-2025; 21-Nov-2026 | 21-Apr-23 |
| 45345667 | Demo text2 | Some text2 | 5545567 | GHH | Mono | 5(q)-I,5(n)-I,5(o)-II,5(g) | Sometext3 | 21-Apr-2023; 21-Sep-2025; 21-Sep-2025; 21-Sep-2025 | 21-Apr-23 |
| 67345668 | Demo text3 | Some text3 | 5545567 | IYT | Turbine | 5(q)-I,5(n)-I | Sometext4 | 21-Apr-2023; 21-Sep-2025 | 21-Apr-23 |
| 78345669 | Demo text4 | Some text4 | 5545567 | VRT | Hydro | 5(q)-I,5(n)-I,5(o)-II | Sometext5 | 21-Apr-2023; 21-Sep-2025; 21-Sep-2025 | 21-Apr-23 |
Kindly Paste this code in your advance editor and check the result.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZBPC4JAEMW/iuxJQSF3d1ajU1CkhzqUBCFeqiWCck3s37dv1yLGpAgv781jYPi9SVPiU8ZBCEFcMpJHZVXyVvk6LNRRvgMABxCBnubJTOv4IDdVud+YlX1yvNgFO3+a0mZcOm7fvhqRZvG0+HXY3KV6pr43LEqP9igbWDosZGECdA4zdTFBoNuMZG5KONQ1A1yT4pq0UXMSRVqnKlc/Ku4cVId1qtPiFEHNGWJOhjlZgzNeJVqTc7ne5/ITFdHxH3QthiCsGfqYgWMG3mBYzg1DdN+WX5+FSODfPzWxsgc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Heading1 = _t, Heading2 = _t, Heading3 = _t, Heading4 = _t, Heading5 = _t, Heading6 = _t, Heading7 = _t, Heading8 = _t, Heading9 = _t, Heading10 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Heading1", Int64.Type}, {"Heading2", type text}, {"Heading3", type text}, {"Heading4", Int64.Type}, {"Heading5", type text}, {"Heading6", type text}, {"Heading7", type text}, {"Heading8", type text}, {"Heading9", type text}, {"Heading10", type date}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Heading7", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Heading7"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Heading7", type text}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Heading9", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Heading9"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Heading9", type date}})
in
#"Changed Type2"
3 Replies
Kindly Paste this code in your advance editor and check the result.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZBPC4JAEMW/iuxJQSF3d1ajU1CkhzqUBCFeqiWCck3s37dv1yLGpAgv781jYPi9SVPiU8ZBCEFcMpJHZVXyVvk6LNRRvgMABxCBnubJTOv4IDdVud+YlX1yvNgFO3+a0mZcOm7fvhqRZvG0+HXY3KV6pr43LEqP9igbWDosZGECdA4zdTFBoNuMZG5KONQ1A1yT4pq0UXMSRVqnKlc/Ku4cVId1qtPiFEHNGWJOhjlZgzNeJVqTc7ne5/ITFdHxH3QthiCsGfqYgWMG3mBYzg1DdN+WX5+FSODfPzWxsgc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Heading1 = _t, Heading2 = _t, Heading3 = _t, Heading4 = _t, Heading5 = _t, Heading6 = _t, Heading7 = _t, Heading8 = _t, Heading9 = _t, Heading10 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Heading1", Int64.Type}, {"Heading2", type text}, {"Heading3", type text}, {"Heading4", Int64.Type}, {"Heading5", type text}, {"Heading6", type text}, {"Heading7", type text}, {"Heading8", type text}, {"Heading9", type text}, {"Heading10", type date}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Heading7", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Heading7"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Heading7", type text}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Heading9", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Heading9"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Heading9", type date}})
in
#"Changed Type2"To reuse the code I need to understand this part
Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)QuoteStyle.Csv is optional argument?
Table.TransformColumns 2nd argument trasformOperations
let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}})What is nullable text and meta [Serialized.Text = true] means?
If my solution helped you then kindly consider this as a Solution and give a thumbs up.
