Power BI Power Query Create Unique Keys from Text string separated by ";"

Question

Background: Our sales order dataset contains a column for "Delivery Number" which typically contains 1 number if the order has been processed and is null if the order is still open. We now have a situation where some orders are coming in with multiple delivery numbers all combined in a text string separated by ";". Some of the delivery numbers are unique and some are duplicates (highlighted below):

My end goal: is to use "delivery number" as a key to join another data table that will provide more detail for each unique delivery. This is the current way I'm thinking of approaching the problem:

1. Remove duplicates from delivery number field
2. Filter for all orders that have more that 1 delivery number
3. Split the column by ";"
4. Pivot the new columns into 1 new Delivery number column
5. Join billings table using the new unique delivery number

Questions:

1. Currently the largest amount of delivery numbers for a single order is 9 but that number could grow. Is there a way to make the formula dynamic to accommodate future scenarios?
2. Is there a simpler way to solve my issue that I'm not thinking of.

Answer 1

Instead of 1, 2, 3 and 4 - you can split directly into rows, then remove duplicates.

Split columns by delimiter into rows