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.
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
Solved! Go to 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
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
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 OrderNo
It should look like this:
Best regards,
Owen
Hi Owen,
Thanks that works perfectly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |