Forum Discussion

wharrison999's avatar
wharrison999
Frequent Visitor
7 years ago
Solved

Custom Column - Order Number extraction from Description Column

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 ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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

OwenAuger's avatar
OwenAuger
Super User
7 years ago

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's avatar
wharrison999
Frequent Visitor
7 years ago

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's avatar
    OwenAuger
    Super User
    7 years ago

    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's avatar
      wharrison999
      Frequent Visitor
      7 years ago

      Hi Owen,

       

      Thanks that works perfectly.