Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Syndicate_Admin
Administrator
Administrator

Extract Numbers from a Column with Multiple Values and Delimiters

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?

1 REPLY 1
AlienSx
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.