Custom Column - Order Number extraction from Description Column

[wharrison999]

Hi Everyone,

Please could someone help me.

I need to create a custom column to extract the order number from a description column.

Unfortuntatly the data I am working with is quite messy so the usual methods like split column does not work.

Below is an example of the difference in my lines within the data. 

The only common denominator is a six digit number.

Description

TEX - AA07ZZZ - O/N 123456

TEXT      ON-345123         ZZ17XXX

TEXT ORDER 999234

TEXTZZZ-BB16AAA-987654/2

The result I would like to see is as below.

Order No

123456

345123

999234

987654

Many Thanks 

Kind Regards

Wendy

[OwenAuger]

Hi Wendy

Here's an example of one way you can extract a 6-digit number from a text string. It converts the Description column to a list, then uses List.Accumulate to build up the number, resetting if the sequence is broken before reaching 6 digits.

Paste this M code into a blank query and you can see how it works.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYxLCsAgEEOvMriuWK2fuhypWwVxISPe/xodCm2zCnlJ5hQ9D5CAuAciYldVAW0O67xY24M7PKpFcsoIXhHpMMb4e7VduUGMkfdfyrcyJe0RUcYzeGeVEWvd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    #"Added Custom" =
      Table.AddColumn(
        #"Changed Type",
        "Order No",
        each
        let
          nums = {"0".."9"},
          CharList = Text.ToList([Description]),
          OrderNo = List.Accumulate(
            CharList, "", (state,current)=>if Text.Length(state)=6 then state else if List.Contains(nums,current) then state & current else ""
          )
        in OrderNo, 
        type text
      )
in
    #"Added Custom"

Best regards,

Owen

[wharrison999]

Hi Owen,

Thank you so much for your reply.

That works perfectly on the example I gave.  However I am struggling to apply it to my live data as it is giving me the same results from the example.

I realise I probably need to change the source data but not quite sure how to approach this.

Can you help?

Many Thanks

Kind Regards

Wendy

[OwenAuger · Accepted solution]

Hi again Wendy,

Sure thing - yes the above is an illustration and the idea is to put the Added Custom step into your own query.

I'm assuming you are starting with a table in the Power Query editor containing a Description column.

Then you should go to the ribbon => Add Column => Custom Column

then enter this code in the dialog box

let
  nums = {"0".."9"},
  CharList = Text.ToList([Description]),
  OrderNo = List.Accumulate(
    CharList, "", (state,current)=>if Text.Length(state)=6 then state else if List.Contains(nums,current) then state & current else ""
  )
in OrderNo

It should look like this:

Best regards,

Owen

[wharrison999]

Hi Owen,

Thanks that works perfectly.