Forum Discussion
Custom Column - Order Number extraction from Description Column
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 OrderNoIt should look like this:
Best regards,
Owen
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
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 OrderNoIt should look like this:
Best regards,
Owen
Hi Owen,
Thanks that works perfectly.