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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
vineshparekh
Helper I
Helper I

Extract only numbers in the brackets

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

3 ACCEPTED SOLUTIONS
collinsg
Super User
Super User

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...

collinsg_0-1707172437818.png

 

 

View solution in original post

dufoq3
Super User
Super User

@vineshparekh

same result as @collinsg, different approach

 

dufoq3_0-1707241096848.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

smpa01
Super User
Super User

@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"

 

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@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"

 

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
dufoq3
Super User
Super User

@vineshparekh

same result as @collinsg, different approach

 

dufoq3_0-1707241096848.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

collinsg
Super User
Super User

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...

collinsg_0-1707172437818.png

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors