Split Two Columns into Rows by their Respective Delimiters.

[PowerBI_Query]

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

[KeyurPatel14]

If my solution helped you then kindly consider this as a Solution and give a thumbs up.

[KeyurPatel14 · Accepted solution]

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"

[PowerBI_Query]

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?