Trying to a 0 prefix to telephone number field - had success the other day. Think I overwrote it...

[Rhothgar]

let
    Source = Csv.Document(File.Contents("C:\Users\roger\OneDrive\testorders.csv"),[Delimiter=",", Columns=118, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product title", type text}, {"Payment total", Int64.Type}, {"Shipping Weight", type text}, {"Length", type text}, {"Width", type text}, {"Height", type text}, {"Full name (delivery)", type text}, {"Company name (delivery)", type text}, {"Address line 1 (delivery)", type text}, {"Address line 2 (delivery)", type text}, {"Town / city (delivery)", type text}, {"County (delivery)", type text}, {"Postcode (delivery)", type text}, {"Country (delivery)", type text}, {"Country code (delivery)", type text}, {"Telephone number (delivery)", Int64.Type}, {"Email address", type text}, {"Order #", type text}, {"Channel", type text}, {"Channel order #", type text}, {"Invoice #", type text}, {"Order date", type datetime}, {"Invoice date", type date}, {"Due date", type date}, {"Delivery date", type date}, {"Despatch date", type date}, {"Status", type text}, {"Custom status", type text}, {"Paid", type text}, {"Paid on", type datetime}, {"Placed by", type text}, {"Product weight", type number}, {"Product commodity code", type text}, {"Product country of origin", type text}, {"Product quantity", Int64.Type}, {"Product warehouse location", Int64.Type}, {"Product frequency", type text}, {"Product instalments", Int64.Type}, {"Product VAT rate", Int64.Type}, {"Product VAT value", Int64.Type}, {"Product price", Int64.Type}, {"Product subtotal", Int64.Type}, {"Order subtotal", Int64.Type}, {"Discount total", Int64.Type}, {"Coupon code", type text}, {"Coupon total", Int64.Type}, {"Weight total", type number}, {"Shipping method", type text}, {"Shipping VAT rate", Int64.Type}, {"Shipping VAT value", Int64.Type}, {"Shipping total", Int64.Type}, {"Order VAT", Int64.Type}, {"DDP duties", Int64.Type}, {"DDP fees", Int64.Type}, {"DDP taxes", Int64.Type}, {"Order total", Int64.Type}, {"Refunded total", Int64.Type}, {"Safe place", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Shipping Weight", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Shipping Weight.1", "Shipping Weight.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Shipping Weight.1", type number}, {"Shipping Weight.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Length", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Length.1", "Length.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Length.1", Int64.Type}, {"Length.2", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Width", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Width.1", "Width.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Width.1", Int64.Type}, {"Width.2", type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "Height", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Height.1", "Height.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Height.1", Int64.Type}, {"Height.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Shipping Weight.2", "Length.2", "Width.2", "Height.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Shipping Weight.1", "Shipping Weight"}, {"Length.1", "Length"}, {"Width.1", "Width"}, {"Height.1", "Height"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Product title", "Product subtotal", "Shipping Weight", "Length", "Width", "Height", "Full name (delivery)", "Company name (delivery)", "Address line 1 (delivery)", "Address line 2 (delivery)", "Town / city (delivery)", "County (delivery)", "Postcode (delivery)", "Country (delivery)", "Country code (delivery)", "Telephone number (delivery)", "Email address", "Order #", "Channel", "Channel order #", "Invoice #", "Order date", "Invoice date", "Due date", "Delivery date", "Despatch date", "Status", "Custom status", "Paid", "Paid on", "Placed by", "Product weight", "Product commodity code", "Product country of origin", "Product quantity", "Product warehouse location", "Product frequency", "Product instalments", "Product VAT rate", "Product VAT value", "Product price", "Order subtotal", "Discount total", "Coupon code", "Coupon total", "Weight total", "Shipping method", "Shipping VAT rate", "Shipping VAT value", "Shipping total", "Order VAT", "DDP duties", "DDP fees", "DDP taxes", "Order total", "Refunded total", "Safe place", "Payment total"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Reordered Columns", "Telephone number (delivery)", "Telephone number (delivery) - Copy"),
    #"Changed Type5" = Table.TransformColumnTypes(#"Duplicated Column",{{"Telephone number (delivery) - Copy", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type5",{"", "_1", "_2", "_3", "_4", "_5", "_6", "_7", "_8", "_9", "_10", "_11", "_12", "_13", "_14", "_15", "_16", "_17", "_18", "_19", "_20", "_21", "_22", "_23", "_24", "_25", "_26", "_27", "_28", "_29", "_30", "_31", "_32", "_33", "_34", "_35", "_36", "_37", "_38", "_39", "_40", "_41", "_42", "_43", "_44", "_45", "_46", "_47", "_48", "_49", "_50", "_51", "_52", "_53", "_54", "_55", "_56", "_57", "_58", "_59"})
in
    #"Removed Columns1"

I am pretty sure the above paste is useless.  I went through some of my earlier posts from 2024 and couldn't find out how to paste in the correct format.  I've even followed a link that tells you how to do it but it threw an HTML error. 

I'll have to look at it again later or perhaps tomorrow when I get more time to read up if it is of no use to anyone wishing to try and help me. SORRY!

The other day I seemingly successful edited lots on the powerquery including adding a 0 prefx to the first telephone number field which is UK format.  So stupidly I either deleted the query or changed something in the connections and now all I receive is an error.  I vaguely recall the code (which I'd generated using Co-Pilot PRE AI) had the number 11 in it because that is the amount of digits in a UK format mobile phone number.

It basically needs to create one unbroken string of 11 characters irrespective of how the number in the source file is written which could be any of the following:-

7966111234 
7966 111 234
07966111234
07966 111 234

796 611 1234

0796 611 1234
0 796 611 1234

You get the gist.  A human will not care what format they have entered into a form. Just so long as it is the correct number (or mistyped and incorrect for that matter...)

[Rhothgar]

SCRATCH THIS POST!

I'll start a clearer more concise one.

Thanks for everyone's advice. I am getting there slowly but surely.

[Rhothgar]

@dufoq3 and @watkinnc 

Ignore above request for now.  FINALLY, here is data in correct format:-

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5ZVtU6MwEMe/Ssa3OpgEKKWvrmW0d7VWrN6p5zg3KeQqNk0YHqz99ibQFkrBq9744uY6gd3s0N3w3/0N9/cHbspCcBp4SSA4GAbzIKH+wdHBQPAkWoIfJGUJjWQA2eCGxgkYEk7ltkcC5gsuPbm6I2QBs9vP3Lvri5uR9L5zlQqcBXzqi7kM9HvyBi271bIswzLk5ikr8pzX+MIFncusmpc9zVPGmsy3ETZ0SzptTQezqXJ04Kl/4ZU1YW43C5vH0DjGEBsAw44OOxBuRXc2dnnTKm1cyn35TnnaOxo3Jk/jTLjygrnJxZVOIa8MkeRRtsCJKIlpXQcaOtXYmC5fJmKx26I9GlNZ9efdPl2/565e8JzEMuSQSJ0uDH7prn27nHzFL/4VTi5PXpAdnpHAswpJkLp0BHHJmhpExskhUv7pxRgjZLfyfzhLj+UNOBXRnO4ed7PkeGx8VcNWv6Ls+sLIrNxh8VCewyWRR5ms51GQTQF84ypyIBNpGNsaMqCGVGWfxrNEhFve7tJxkSZ7R+CkE0aXABmgx1Iqe+fHwCXPdE55AnrBjK6efzi6VwnGlFMKxiKNY0pSGbCgA5xHElImo8TfVOqOLAAH473ANU1sGW2IlJKRKhCt8r+X3PaHyUUdhD6N3CJ5M7mZsMoW0uZaO6nwRJ3yTeJX6Fy3YR/xK6vxTKVAQWc3DBmVs7PcwHl32y/ghINLezqzR7tw6iVbA2f7n4OzjTQELQ1ZWDKqgnMxCRitsrlYLLSpEFNG5Xxr6ewdrLoBn/2R1WEQE3BNoknqqdx6C5xT4i+yBq2nwwRoMNwDUl0zdYTQCcwHhcnUWpKnfi+k9ochxR2ofxqkRfJmSJWi0hSaZhKrjyTJZa1IXqN6hc61/m+rflhHZ/1hiu1bZP68skpkPtlXye/b5P8g05YfzVZbQybcD8ytafw7LB9eAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Recipient Business Name" = _t, #"Recipient Name" = _t, #"Recipient Address Line 1" = _t, #"Recipient Address Line 2" = _t, #"Recipient Address Line 3 (County)" = _t, #"Recipient Postcode" = _t, #"Recipient Town" = _t, #"Country (delivery)" = _t, #"Country code (delivery)" = _t, #"Recipient Mobile Phone" = _t, #"Recipient Email Address" = _t, #"Special Instructions 1" = _t, #"Special Instructions 2" = _t, #"Special Instructions 3" = _t, #"Special Instructions 4" = _t, #"Reference Number" = _t, Weight = _t, Length = _t, Width = _t, Height = _t, Channel = _t, #"Channel order #" = _t, #"Invoice #" = _t, #"Order date" = _t, #"Invoice date" = _t, #"Due date" = _t, #"Delivery date" = _t, #"Despatch date" = _t, Status = _t, #"Custom status" = _t, Paid = _t, #"Paid on" = _t, #"Placed by" = _t, #"Completed on" = _t, #"Completed by" = _t, #"Allow contact" = _t, #"User group" = _t, #"Account ID" = _t, Title = _t, #"First name" = _t, #"Last name" = _t, #"Full title" = _t, #"Full name" = _t, #"Company name" = _t, #"Address line 1" = _t, #"Address line 2" = _t, #"Town / city" = _t, County = _t, Postcode = _t, Country = _t, #"Country code" = _t, #"Telephone number" = _t, #"VAT number" = _t, #"EORI number" = _t, #"Facebook ID" = _t, #"Delivery address" = _t, #"Title (delivery)" = _t, #"First name (delivery)" = _t, #"Last name (delivery)" = _t, #"Full title (delivery)" = _t, #"Currency code" = _t, #"VAT rate" = _t, #"Payment method" = _t, #"Payment ID" = _t, #"Voucher code" = _t, #"Voucher total" = _t, #"Product lines" = _t, #"Product SKU" = _t, #"Product parent" = _t, #"Product bar code" = _t, #"Product part number" = _t, #"Product variant" = _t, #"Product category" = _t, #"Product brand" = _t, #"Product supplier" = _t, #"Product drop shipper" = _t, #"Product intangible" = _t, #"Product file" = _t, #"Product file group" = _t, #"Product bundle" = _t, #"Product bundled" = _t, #"Product gift voucher" = _t, #"Product weight" = _t, #"Product commodity code" = _t, #"Product country of origin" = _t, #"Product quantity" = _t, #"Product warehouse location" = _t, #"Product frequency" = _t, #"Product instalments" = _t, #"Product VAT rate" = _t, #"Product VAT value" = _t, #"Product price" = _t, #"Product subtotal" = _t, #"Order subtotal" = _t, #"Discount total" = _t, #"Coupon code" = _t, #"Coupon total" = _t, #"Weight total" = _t, #"Shipping method" = _t, #"Shipping VAT rate" = _t, #"Shipping VAT value" = _t, #"Shipping total" = _t, #"Order VAT" = _t, #"DDP duties" = _t, #"DDP fees" = _t, #"DDP taxes" = _t, #"Order total" = _t, #"Refunded total" = _t, #"IP address" = _t, #"Device type" = _t, #"Site version" = _t, Affiliate = _t, #"Referring domain" = _t, #"Referring search" = _t, #"Customer comments" = _t, #"Admin comments" = _t, Additions = _t, #"Product list" = _t, #"Product full" = _t, #"Product title" = _t, #"Payment total" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Recipient Business Name", type text}, {"Recipient Name", type text}, {"Recipient Address Line 1", type text}, {"Recipient Address Line 2", type text}, {"Recipient Address Line 3 (County)", type text}, {"Recipient Postcode", type text}, {"Recipient Town", type text}, {"Country (delivery)", type text}, {"Country code (delivery)", type text}, {"Recipient Mobile Phone", Int64.Type}, {"Recipient Email Address", type text}, {"Special Instructions 1", type text}, {"Special Instructions 2", type text}, {"Special Instructions 3", type text}, {"Special Instructions 4", type text}, {"Reference Number", type text}, {"Weight", type text}, {"Length", type text}, {"Width", type text}, {"Height", type text}, {"Channel", type text}, {"Channel order #", type text}, {"Invoice #", type text}, {"Order date", type datetime}, {"Invoice date", type date}, {"Due date", type date}, {"Delivery date", type date}, {"Despatch date", type date}, {"Status", type text}, {"Custom status", type text}, {"Paid", type text}, {"Paid on", type datetime}, {"Placed by", type text}, {"Completed on", type text}, {"Completed by", type text}, {"Allow contact", type text}, {"User group", type text}, {"Account ID", Int64.Type}, {"Title", type text}, {"First name", type text}, {"Last name", type text}, {"Full title", type text}, {"Full name", type text}, {"Company name", type text}, {"Address line 1", type text}, {"Address line 2", type text}, {"Town / city", type text}, {"County", type text}, {"Postcode", type text}, {"Country", type text}, {"Country code", type text}, {"Telephone number", Int64.Type}, {"VAT number", type text}, {"EORI number", type text}, {"Facebook ID", type text}, {"Delivery address", type text}, {"Title (delivery)", type text}, {"First name (delivery)", type text}, {"Last name (delivery)", type text}, {"Full title (delivery)", type text}, {"Currency code", type text}, {"VAT rate", Int64.Type}, {"Payment method", type text}, {"Payment ID", type text}, {"Voucher code", type text}, {"Voucher total", Int64.Type}, {"Product lines", Int64.Type}, {"Product SKU", Int64.Type}, {"Product parent", Int64.Type}, {"Product bar code", Int64.Type}, {"Product part number", type text}, {"Product variant", type text}, {"Product category", type text}, {"Product brand", type text}, {"Product supplier", type text}, {"Product drop shipper", type text}, {"Product intangible", type text}, {"Product file", type text}, {"Product file group", type text}, {"Product bundle", type text}, {"Product bundled", type text}, {"Product gift voucher", type text}, {"Product weight", type number}, {"Product commodity code", type text}, {"Product country of origin", type text}, {"Product quantity", Int64.Type}, {"Product warehouse location", Int64.Type}, {"Product frequency", type text}, {"Product instalments", Int64.Type}, {"Product VAT rate", Int64.Type}, {"Product VAT value", Int64.Type}, {"Product price", Int64.Type}, {"Product subtotal", Int64.Type}, {"Order subtotal", Int64.Type}, {"Discount total", Int64.Type}, {"Coupon code", type text}, {"Coupon total", Int64.Type}, {"Weight total", type number}, {"Shipping method", type text}, {"Shipping VAT rate", Int64.Type}, {"Shipping VAT value", Int64.Type}, {"Shipping total", Int64.Type}, {"Order VAT", Int64.Type}, {"DDP duties", Int64.Type}, {"DDP fees", Int64.Type}, {"DDP taxes", Int64.Type}, {"Order total", Int64.Type}, {"Refunded total", Int64.Type}, {"IP address", type text}, {"Device type", type text}, {"Site version", type text}, {"Affiliate", type text}, {"Referring domain", type text}, {"Referring search", type text}, {"Customer comments", type text}, {"Admin comments", type text}, {"Additions", type text}, {"Product list", Int64.Type}, {"Product full", Int64.Type}, {"Product title", type text}, {"Payment total", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(#"Changed Type", "Custom", each if [Recipient Business Name] = null then [Recipient Name] else [Recipient Business Name])),
Custom1 = #"Added Custom"{0}[Custom]
in
Custom1

OK. Cool. So

What I am trying to do now is edit "Recipient Business Name" to take the value from "Company Name (delivery)" and propagate that BUT where this is no "Company Name" then take the values from "Recipient Name" instead.

I've added a custom column at the end with some code but earlier in the code I renamed "Company name (delivery)" to "Recipient Business Name".  Maybe this is where the fault lies but I simply cannot track it down.

So maybe I messed the steps up.

[watkinnc]

I would use Replace Values to replace spaces with no value. Then Clean it just in case, then use the Text.PadStart([Phone], 11, "0") expression.

--Nate 

[Rhothgar]

This was a really useful advice.

Managed to achieve what I wanted to in that column for now and tidied up some other things.

And also managed to re-learn how to post data correctly so I will do that now so dufoq3 can also see it on a separate message.

[dufoq3]

Hi @Rhothgar, different approach here.

You can achieve requested by adding this code as custom column.
This code extracts only number and adds leading zeros if needed.
Just replace [Column1] to your column name.

Text.PadStart(Text.Select(Text.From([Column1]), {"0".."9"}), 11, "0")

[Rhothgar]

It works! Thank you.

I get an extra zero on Rows 9 and 12?  Source code shows one ZERO at beginning of those rows.

I can see this is because my source data has only 10 digits on those two rows.

Is it possible to make the code such that if an error exists ie less than 11 digits is fed from source code that it returns a blank cell instead please?

Would it simply be shown as "null" in a blank cell that has had a function applied?

I have just run it on a bigger dataset now and can see other issues.  Sometimes country codes are used like +44, or 0044, or simply 44.  I think it would be a big task to remove any possible country options?

[dufoq3]

Provide sample data with as many cases as you can and also expected result based on sample data. If you don't know how to properly provide sample data - read note below my post please.

[Rhothgar]

I actually did read that before I started this post initially and I ended up with the result I had on the first post which I wasn't convinced looked correct.

I think I will have to go back through my very first post in January and see if I can work out how to do it so it gives that encoded binary source.  I think that is what you are looking for?

I know at one point I uploaded a file to my OneDrive.  I am not sure if that was because I simply couldn't work it out at all how to provide data.

I did a basic transform last night to remove 44 and replace with 0 but that was easy.  I know wanting handle ISO country codes would be really challenging.  I suppose when someone types in +36 in a form we know it is Hungary and it is easy to remove + and add 00 instead but how to work this for any country I wouldn't have a clue.  

To complicate matters, it seems that the source data does not ever include a + sign at the beginning of a country code!

EDIT:

Of course, I am getting confused! ISO-3166 which is what I was alluding to is the 2 digit country code but what I meant was this:-

https://en.wikipedia.org/wiki/List_of_country_calling_codes

So this made me think it isn't as difficult, perhaps, as I first thought because the source data has a column for ISO-3166 country codes!  So from that we know that the start of the telephone number provided may contain a 0 and the country code can be extrapolated from a list of country calling codes.

My problem is how to put these two things together BUT primarily I am really concerned with UK numbers at the moment and keeping it simple.

Naturally, it would be nice to be able to handle UK landlines too either by nulling them out or bringing them into line with mobile numbers.

This illustrates all the codes for landlines:-

https://en.wikipedia.org/wiki/List_of_dialling_codes_in_the_United_Kingdom

But this part PROBABLY? simplifies it:-

I'll have to have a good think about getting my head around that so, as before, I think it is best to stick to just UK mobile numbers for now as they are so easy to identify.

[dufoq3]

In previous reply you mentioned: "I get an extra zero on Rows 9 and 12?", but I don't see your data or link etc. If you do not provide any sample I can't help you anymore.

[Rhothgar]

I now do not even see those errors on rows 9 and 12.  I have overwritten stuff accidentally through confusion.

I hope all of the following is in the correct format you have asked for.  As before, I did try to follow your link but it made no sense to me iro sample data.  I cannot get binary 64 code for the life of me.  I've tried.  I am sure you showed me how to do it properly before in a post but I couldn't find the post.  I should have bookmarked it for ease of reference!

I am 100% sure that, last night, I was seeing more than what I can now see below like the double zeros in Rows 9 and 12 which have now vanished (for vanished read deleted or accidentally overwritten).

This is what I now see:-



This is what is in source file (left column is actually what is there, right column is what I am trying but failing to get to):-



And this is the current code I have:-

let
    Source = Csv.Document(File.Contents("C:\Users\roger\OneDrive\testorders.csv"),[Delimiter=",", Columns=118, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers2" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers2",{{"Product title", type text}, {"Payment total", Int64.Type}, {"Shipping Weight", type text}, {"Length", type text}, {"Width", type text}, {"Height", type text}, {"Full name (delivery)", type text}, {"Company name (delivery)", type text}, {"Address line 1 (delivery)", type text}, {"Address line 2 (delivery)", type text}, {"Town / city (delivery)", type text}, {"County (delivery)", type text}, {"Postcode (delivery)", type text}, {"Country (delivery)", type text}, {"Country code (delivery)", type text}, {"Telephone number (delivery)", Int64.Type}, {"Email address", type text}, {"Order #", type text}, {"Channel", type text}, {"Channel order #", type text}, {"Invoice #", type text}, {"Order date", type datetime}, {"Invoice date", type date}, {"Due date", type date}, {"Delivery date", type date}, {"Despatch date", type date}, {"Status", type text}, {"Custom status", type text}, {"Paid", type text}, {"Paid on", type datetime}, {"Placed by", type text}, {"Product weight", type number}, {"Product commodity code", type text}, {"Product country of origin", type text}, {"Product quantity", Int64.Type}, {"Product warehouse location", Int64.Type}, {"Product frequency", type text}, {"Product instalments", Int64.Type}, {"Product VAT rate", Int64.Type}, {"Product VAT value", Int64.Type}, {"Product price", Int64.Type}, {"Product subtotal", Int64.Type}, {"Order subtotal", Int64.Type}, {"Discount total", Int64.Type}, {"Coupon code", type text}, {"Coupon total", Int64.Type}, {"Weight total", type number}, {"Shipping method", type text}, {"Shipping VAT rate", Int64.Type}, {"Shipping VAT value", Int64.Type}, {"Shipping total", Int64.Type}, {"Order VAT", Int64.Type}, {"DDP duties", Int64.Type}, {"DDP fees", Int64.Type}, {"DDP taxes", Int64.Type}, {"Order total", Int64.Type}, {"Refunded total", Int64.Type}, {"Safe place", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Shipping Weight", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Shipping Weight.1", "Shipping Weight.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Shipping Weight.1", type number}, {"Shipping Weight.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Length", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Length.1", "Length.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Length.1", Int64.Type}, {"Length.2", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Width", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Width.1", "Width.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Width.1", Int64.Type}, {"Width.2", type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "Height", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Height.1", "Height.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Height.1", Int64.Type}, {"Height.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Shipping Weight.2", "Length.2", "Width.2", "Height.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Shipping Weight.1", "Shipping Weight"}, {"Length.1", "Length"}, {"Width.1", "Width"}, {"Height.1", "Height"}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Renamed Columns"),
    #"Added Custom" = Table.AddColumn(#"Demoted Headers", "Custom", each Text.PadStart(Text.Select(Text.From([Column16]), {"0".."9"}), 11, "0")),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Added Custom", [PromoteAllScalars=true]),
    #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers1",{{"Telephone number (delivery)", "Telephone number (delivery).old"}, {"00000000000", "Telephone number (delivery)"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Product title", "Payment total", "Shipping Weight", "Length", "Width", "Height", "Full name (delivery)", "Company name (delivery)", "Address line 1 (delivery)", "Address line 2 (delivery)", "Town / city (delivery)", "County (delivery)", "Postcode (delivery)", "Country (delivery)", "Country code (delivery)", "Telephone number (delivery)", "Email address", "Order #", "Channel", "Channel order #", "Invoice #", "Order date", "Invoice date", "Due date", "Delivery date", "Despatch date", "Status", "Custom status", "Paid", "Paid on", "Placed by", "Product weight", "Product commodity code", "Product country of origin", "Product quantity", "Product warehouse location", "Product frequency", "Product instalments", "Product VAT rate", "Product VAT value", "Product price", "Product subtotal", "Order subtotal", "Discount total", "Coupon code", "Coupon total", "Weight total", "Shipping method", "Shipping VAT rate", "Shipping VAT value", "Shipping total", "Order VAT", "DDP duties", "DDP fees", "DDP taxes", "Order total", "Refunded total", "Safe place", "", "_1", "_2", "_3", "_4", "_5", "_6", "_7", "_8", "_9", "_10", "_11", "_12", "_13", "_14", "_15", "_16", "_17", "_18", "_19", "_20", "_21", "_22", "_23", "_24", "_25", "_26", "_27", "_28", "_29", "_30", "_31", "_32", "_33", "_34", "_35", "_36", "_37", "_38", "_39", "_40", "_41", "_42", "_43", "_44", "_45", "_46", "_47", "_48", "_49", "_50", "_51", "_52", "_53", "_54", "_55", "_56", "_57", "_58", "_59", "Telephone number (delivery).old"})
in
    #"Reordered Columns"

And this is what I see inside PQ UI:-

[dufoq3]

I'm not sure if you understand that pasting your query without source data is totally useless. Look at the Source step please - you are refering your local file...

C:\Users\roger\OneDrive\testorders.csv

[Rhothgar]

As above. This is what I am struggling to get to.  I know it needs to be that binary 64 format or whatever it is called but despite reading various topics, I cannot see to do it but I know I have done before.  I think it probably took me 5 - 6 hours in January to finally achieve it but I have since forgotten!  The joys of old age!

I have just found the file with the Rows9 and 12 errors but I understand until I can work out how to provide proper independent sample data then I cannot find help.

I am now also missing source code column headers too!!!!  It is not going very well.

[Rhothgar]

Hi dufoq3,

How are you keeping?

I will try this also.  I have just this minute dropped back onto it again.

Let's see how it goes.

[AlienSx]

   Table.TransformColumns(
        your_table, 
        {"Telephone number (delivery)", 
        (x) => Text.PadStart(
            Text.Combine(
                Splitter.SplitTextByWhitespace()(x)
            ), 11, "0"
        )}
    )

[Rhothgar]

Thank you. I will attempt to integrate this.  The other solution sort of worked in isolation but wasn't 100%.

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrc0MzM0NDQyNlFQitWB8BWAAgpAEbCAAUIFgo+iAiigAFShgKIETUQBVSgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entered = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"Entered"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Entered],each Text.End("00000"&[Entered],11),Replacer.ReplaceValue,{"Entered"})
in
    #"Replaced Value1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Rhothgar]

Thanks. Sort of got it to work in isolation but as soon as I added it into original query it bombed out giving a token error of "in" when that is there and it underlined the quote mark at the end.  No amount of deletion or inserting resolved it.

let orders = let Source = Csv.Document(File.Contents("C:\Users\roger\OneDrive\testorders.csv"),[Delimiter=",", Columns=118, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product title", type text}, {"Payment total", Int64.Type}, {"Shipping Weight", type text}, {"Length", type text}, {"Width", type text}, {"Height", type text}, {"Full name (delivery)", type text}, {"Company name (delivery)", type text}, {"Address line 1 (delivery)", type text}, {"Address line 2 (delivery)", type text}, {"Town / city (delivery)", type text}, {"County (delivery)", type text}, {"Postcode (delivery)", type text}, {"Country (delivery)", type text}, {"Country code (delivery)", type text}, {"Telephone number (delivery)", Int64.Type}, {"Email address", type text}, {"Order #", type text}, {"Channel", type text}, {"Channel order #", type text}, {"Invoice #", type text}, {"Order date", type datetime}, {"Invoice date", type date}, {"Due date", type date}, {"Delivery date", type date}, {"Despatch date", type date}, {"Status", type text}, {"Custom status", type text}, {"Paid", type text}, {"Paid on", type datetime}, {"Placed by", type text}, {"Product weight", type number}, {"Product commodity code", type text}, {"Product country of origin", type text}, {"Product quantity", Int64.Type}, {"Product warehouse location", Int64.Type}, {"Product frequency", type text}, {"Product instalments", Int64.Type}, {"Product VAT rate", Int64.Type}, {"Product VAT value", Int64.Type}, {"Product price", Int64.Type}, {"Product subtotal", Int64.Type}, {"Order subtotal", Int64.Type}, {"Discount total", Int64.Type}, {"Coupon code", type text}, {"Coupon total", Int64.Type}, {"Weight total", type number}, {"Shipping method", type text}, {"Shipping VAT rate", Int64.Type}, {"Shipping VAT value", Int64.Type}, {"Shipping total", Int64.Type}, {"Order VAT", Int64.Type}, {"DDP duties", Int64.Type}, {"DDP fees", Int64.Type}, {"DDP taxes", Int64.Type}, {"Order total", Int64.Type}, {"Refunded total", Int64.Type}, {"Safe place", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Shipping Weight", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Shipping Weight.1", "Shipping Weight.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Shipping Weight.1", type number}, {"Shipping Weight.2", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Length", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Length.1", "Length.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Length.1", Int64.Type}, {"Length.2", type text}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Width", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Width.1", "Width.2"}), #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Width.1", Int64.Type}, {"Width.2", type text}}), #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "Height", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Height.1", "Height.2"}), #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Height.1", Int64.Type}, {"Height.2", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Shipping Weight.2", "Length.2", "Width.2", "Height.2"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Shipping Weight.1", "Shipping Weight"}, {"Length.1", "Length"}, {"Width.1", "Width"}, {"Height.1", "Height"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Product title", "Product subtotal", "Shipping Weight", "Length", "Width", "Height", "Full name (delivery)", "Company name (delivery)", "Address line 1 (delivery)", "Address line 2 (delivery)", "Town / city (delivery)", "County (delivery)", "Postcode (delivery)", "Country (delivery)", "Country code (delivery)", "Telephone number (delivery)", "Email address", "Order #", "Channel", "Channel order #", "Invoice #", "Order date", "Invoice date", "Due date", "Delivery date", "Despatch date", "Status", "Custom status", "Paid", "Paid on", "Placed by", "Product weight", "Product commodity code", "Product country of origin", "Product quantity", "Product warehouse location", "Product frequency", "Product instalments", "Product VAT rate", "Product VAT value", "Product price", "Order subtotal", "Discount total", "Coupon code", "Coupon total", "Weight total", "Shipping method", "Shipping VAT rate", "Shipping VAT value", "Shipping total", "Order VAT", "DDP duties", "DDP fees", "DDP taxes", "Order total", "Refunded total", "Safe place", "Payment total"}), #"Replaced Value" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"Telephone number (delivery)"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each "Telephone number (delivery)",each Text.PadStart("0" & "Telephone number (delivery)",11),Replacer.ReplaceValue,{"Telephone number (delivery)"}), #"Changed Type5" = Table.TransformColumnTypes(#"Replaced Value1",{{"Telephone number (delivery)", type text}}) in #"Changed Type5"