Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

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 c...
AlienSx's avatar
AlienSx
Super User
3 years ago

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