Extract only numbers in the brackets

[vineshparekh]

Hi, 

I have this information in one cell of Excel and I want to only extract the numbers within parenthesis ( ). I have made these numbers in Bold characteres. Is it possible to extract using Power Query?

2 Accounts: 9,462.62 CAD (50%) COA_PAI, PAI-Pilots -Crew lodging -0, Comp: PAI (10), Dept: Pilots (110), Acct: Crew lodging (50220), Ident: 0 (0000); 9,462.62 CAD (50%) COA_PAI, PAI-Flt Attd -Crew lodging -0, Comp: PAI (10), Dept: Flt Attd (120), Acct: Crew lodging (50220), Ident: 0 (0000)

Thanks,

Vinesh Parekh

[collinsg · Accepted solution]

Good day vineshparekh,

In the following the steps are:

1. Add a column which has the text split into a list by the delimiter ")".
2. For each item in the list remove the text before "(".
3. Select items in the list which don't contain "%" and aren't empty.

The resulting column contains a list of the values in brackets. The list may be used as required e.g. expanded to rows. The interesting code starts at the step #"Add column, splitting before )".

Hope this helps

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "lU5dC4IwFP0rByHYYMY2SsiexiTwKd9FelARYW6Si/5+14Kgp+o+HLjng3PqOtEwbRtuPi45DmKX6W2mYU0BtpcbDns2l8qUAgRpNboQF6T22t/hQjeMfkAqBWyY5ny1gCnJBYp+jvS/7Ew9Oaoh7iNKFVqvWtn1nkQJJun48euSk4swMXY/b3kHmNJ/rkma5gE=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t]
    ),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}}),
    #"Add column, splitting before )" = Table.AddColumn(
        #"Changed Type", "Items in brackets", each Text.SplitAny([Column1], ")")
    ),
    #"Remove text before (" = Table.TransformColumns(
        #"Add column, splitting before )",
        {{"Items in brackets", each List.Transform(_, each Text.AfterDelimiter(_, "("))}}
    ),
    #"Remove items with % or empty" = Table.TransformColumns(
        #"Remove text before (",
        {{"Items in brackets", each List.Select(_, each not (Text.Contains(_, "%")) and _ <> "")}}
    )
in
    #"Remove items with % or empty"

Result...

[dufoq3 · Accepted solution]

@vineshparekh, 

same result as @collinsg, different approach

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lU5dC4IwFP0rByHYYMY2SsiexiTwKd9FelARYW6Si/5+14Kgp+o+HLjng3PqOtEwbRtuPi45DmKX6W2mYU0BtpcbDns2l8qUAgRpNboQF6T22t/hQjeMfkAqBWyY5ny1gCnJBYp+jvS/7Ew9Oaoh7iNKFVqvWtn1nkQJJun48euSk4swMXY/b3kHmNJ/rkma5gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_NumbersFromBrackets = Table.AddColumn(Source, "Numbers from brackets", each [   openBracketPos =  Text.PositionOf([Column1], "(", Occurrence.All),
            closeBracketPos = Text.PositionOf([Column1], ")", Occurrence.All),
            posForExtract = List.Zip({openBracketPos, closeBracketPos}),
            rangePositions = List.Transform(
                                    posForExtract,
                                    each {_{0} + 1, _{1} - _{0} - 1}
                                ),
            extract =   
                List.Accumulate(
                            List.Buffer(rangePositions),
                            {},
                            (s,c)=> s & {Text.Range([Column1], c{0}, c{1})}
                        ),
            numbersOnly =   
                List.RemoveNulls(
                    List.Transform(extract, (x)=> 
                        try Text.Combine(List.Transform(Text.ToList(x), (y)=> Text.From(Number.From(y)) )) otherwise null
                    )
                )
                          
        ][numbersOnly], type list),
    Ad_NumbersOneLine = Table.AddColumn(Ad_NumbersFromBrackets, "Numbers One Line", each Text.Combine([Numbers from brackets], ", "), type text)
in
    Ad_NumbersOneLine

[smpa01 · Accepted solution]

@vineshparekh  please try this

let
  //main function to apply to forEach element in a table
  fnMaster = (x as text) =>
    let
      fnOne = (x as text) => Text.PositionOf(x, ")"), 
      fnTwo = (x as text) => Text.Trim(x), 
      fnThree = (x as text, y as number) => Text.Start(x, y), 
      fnFour = (x as text)=> Text.Select(x,{"0".."9"})=x, //returns falsy if text contains any value other than 0 to 9
      str = Text.Split(x, "("),  /*split the text by ( - step1 */
      trim = List.Transform(str, each fnTwo(_)),  /*trim each text from step2 */
      pos = List.Transform(trim, each fnOne(_)), /*whats the position of ) in each step2 text step3*/
      element = List.Select(
        List.Transform(trim, each try fnThree(_, pos{List.PositionOf(trim, _)}) otherwise null) 
        , each _ <> null
      ), /*extract the text from each trim text by the position retrieved at step3*/
      onlyNum = List.Select(element,each fnFour(_)) /*take only nums from step3*/

    in
      onlyNum, 
  Source
    = "2 Accounts: 9,462.62 CAD (50%) COA_PAI, PAI-Pilots -Crew lodging -0, Comp: PAI (10), Dept: Pilots (110), Acct: Crew lodging (50220), Ident: 0 (0000); 9,462.62 CAD (50%) COA_PAI, PAI-Flt Attd -Crew lodging -0, Comp: PAI (10), Dept: Flt Attd (120), Acct: Crew lodging (50220), Ident: 0 (0000)", 
  #"Converted to Table" = #table(1, {{Source}}), 
  #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each fnMaster([Column1])), 
  #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
   
in
   #"Expanded Custom"

[dufoq3 · Accepted solution]

@vineshparekh, 

same result as @collinsg, different approach

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lU5dC4IwFP0rByHYYMY2SsiexiTwKd9FelARYW6Si/5+14Kgp+o+HLjng3PqOtEwbRtuPi45DmKX6W2mYU0BtpcbDns2l8qUAgRpNboQF6T22t/hQjeMfkAqBWyY5ny1gCnJBYp+jvS/7Ew9Oaoh7iNKFVqvWtn1nkQJJun48euSk4swMXY/b3kHmNJ/rkma5gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_NumbersFromBrackets = Table.AddColumn(Source, "Numbers from brackets", each [   openBracketPos =  Text.PositionOf([Column1], "(", Occurrence.All),
            closeBracketPos = Text.PositionOf([Column1], ")", Occurrence.All),
            posForExtract = List.Zip({openBracketPos, closeBracketPos}),
            rangePositions = List.Transform(
                                    posForExtract,
                                    each {_{0} + 1, _{1} - _{0} - 1}
                                ),
            extract =   
                List.Accumulate(
                            List.Buffer(rangePositions),
                            {},
                            (s,c)=> s & {Text.Range([Column1], c{0}, c{1})}
                        ),
            numbersOnly =   
                List.RemoveNulls(
                    List.Transform(extract, (x)=> 
                        try Text.Combine(List.Transform(Text.ToList(x), (y)=> Text.From(Number.From(y)) )) otherwise null
                    )
                )
                          
        ][numbersOnly], type list),
    Ad_NumbersOneLine = Table.AddColumn(Ad_NumbersFromBrackets, "Numbers One Line", each Text.Combine([Numbers from brackets], ", "), type text)
in
    Ad_NumbersOneLine

[collinsg · Accepted solution]

Good day vineshparekh,

In the following the steps are:

1. Add a column which has the text split into a list by the delimiter ")".
2. For each item in the list remove the text before "(".
3. Select items in the list which don't contain "%" and aren't empty.

The resulting column contains a list of the values in brackets. The list may be used as required e.g. expanded to rows. The interesting code starts at the step #"Add column, splitting before )".

Hope this helps

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "lU5dC4IwFP0rByHYYMY2SsiexiTwKd9FelARYW6Si/5+14Kgp+o+HLjng3PqOtEwbRtuPi45DmKX6W2mYU0BtpcbDns2l8qUAgRpNboQF6T22t/hQjeMfkAqBWyY5ny1gCnJBYp+jvS/7Ew9Oaoh7iNKFVqvWtn1nkQJJun48euSk4swMXY/b3kHmNJ/rkma5gE=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t]
    ),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}}),
    #"Add column, splitting before )" = Table.AddColumn(
        #"Changed Type", "Items in brackets", each Text.SplitAny([Column1], ")")
    ),
    #"Remove text before (" = Table.TransformColumns(
        #"Add column, splitting before )",
        {{"Items in brackets", each List.Transform(_, each Text.AfterDelimiter(_, "("))}}
    ),
    #"Remove items with % or empty" = Table.TransformColumns(
        #"Remove text before (",
        {{"Items in brackets", each List.Select(_, each not (Text.Contains(_, "%")) and _ <> "")}}
    )
in
    #"Remove items with % or empty"

Result...