Splitting semi colon separated columns into rows

[mds123]

Hello,

My data source is a list of work orders. Two of my columns in my dataset have semi colons to separate the item codes and quantities that are in the same work order. My goal is to break this items and corresponding quantities out onto different rows. This way, each work order / item code combination is its own row. 

This is how the data arrives to me and I can not change how it arrives.

Date	Site	Work Order	Item Codes	Quantities
1/1/2024	CA	1A	1234; 12	1;2
1/1/2024	NY	1B	25	35
1/2/2024	FL	2C	1234; 15; 9	5;2;3

The most items in one work order is likely five but there is no hard limit. My goal is to format the data like this:

Date	Site	Work Order	Item Code	Quantity
1/1/2024	CA	1A	1234	1
1/1/2024	CA	1A	12	2
1/1/2024	NY	1B	25	35
1/2/2024	FL	2C	1234	5
1/2/2024	FL	2C	15	2
1/2/2024	FL	2C	9	3

I'm very lost at how to do this in power query. I tried joining it onto itself in different ways but that did not help.

Any help is appreciated!

[mds123]

If anyone has a similar question, I used this video to solve it. It took maybe 5 minutes. https://www.youtube.com/watch?v=JMOnr3DOqyk 

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJR0lFydgQShmDCyNjEWsHQCMS0NlKK1UFR5xcJEncCEkamQMLYFKrACKbAzQck54wwyNRawRLIM7U2sjZWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Site = _t, #"Work Order" = _t, #"Item Codes" = _t, Quantities = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Site", type text}, {"Work Order", type text}, {"Item Codes", type text}, {"Quantities", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Zip({Text.Split([Item Codes],";"),Text.Split([Quantities],";")})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "Site", "Work Order", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Item Code", "Quantity"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item Code", Int64.Type}, {"Quantity", Int64.Type}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.