Extract Numbers from a Column with Multiple Values and Delimiters

[Syndicate_Admin]

Hello,

I receive third party reports weekly and one of the columns looks like this:

In order to use in my Power BI, I need to extract or split this column so each 'charge' has its own column. Additionally, I only want the numbers to appear in the column values, while the charge name in the header, like so:

I'm not sure how to combat this because (1) the charges can be in variable positions cell-to-cell (see ex rows 2/3), (2) not all cells have the same charges (some cells can have 4 charges while some have 2), and (3) the numbers I want to extract are varying in size (i.e. they can be 6, 5, or 4 characters long).

I am very new to Power Query, but this is roughly what I believe I need:

*For new column named "Oversized Surcharges"*

If [Charge Description] contains "Oversized", then extract the numbers between "Oversized" and the first "|" after "Oversized" (Starting from the right).

How can this be done in Power Query?

[AlienSx]

Hello, 

let
    Source = your_table,
    f = (t as text) =>
    [a = Splitter.SplitTextByDelimiter(" | ")(t),
    b = List.Transform(a, (x) => Record.AddField([], Text.AfterDelimiter(x, " "), Text.BeforeDelimiter(x, " "))),
    c = Record.Combine(b)][c],
    d = Table.AddColumn(Source, "d", each f([Charge Description (Example)])),
    e = List.Buffer(List.Distinct(List.Combine(List.Transform(d[d], Record.FieldNames)))),
    g = Table.ExpandRecordColumn(d, "d", e)
in
    g