Forum Discussion

banksbc's avatar
banksbc
New Member
2 years ago

Capture Number after finding varying prefix

I am very new to Power Query so forgive my ignorance.  I am trying to capture building numbers in its own column out of trouble ticket descriptions.  The issue is that customers put building number in the description in varying ways.  It can be entered with a "B.1550", "BLDG 1550", or B1550".  In the end I only need to capture "1550" in the building column.

Sample Description(s):

MAC - Data Drop Adds - B. 1550

Need to activate Data Ports in (( BLDG-2061 )) for to double our points of sale at each of the kiosks.

Install rack mounted power bar for network switch in B2655 TR 107



Need Help

3 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi banksbc ,

    try this:

     

    Result

    let
    Source = #table(null, {{"B.1550"},{"BLDG 1550"}, {"B1550"}}),
    Ad_ExtractNumbers = Table.AddColumn(Source, "Extract Numbers", each Text.Select([Column1], {"0".."9"}), type text)
in
    Ad_ExtractNumbers
    • banksbc's avatar
      banksbc
      New Member
      2 years ago

      Thank you Dufoq3,
      The issue is the building number varies as shown in the sample descriptions.  Those were listed to show what types of data I am having to parse through.

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Hi, my will extract all numbers from that description. If you want to extrat something different, provide expected result based on sample data please. Think in advance which all possible situations you could face and update sample data if needed.