Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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...)
SCRATCH THIS POST!
I'll start a clearer more concise one.
Thanks for everyone's advice. I am getting there slowly but surely.
Hi @Rhothgar ,
I see you say to close the current thread and open a new one. So please select one or more replies in the current post that are helpful to you and mark them as Solutions, so that more users can see them and learn, and it's a testament to everyone's efforts, thank you!
Best Regards,
Dino Tao
@dufoq3 and @watkinnc
Ignore above request for now. FINALLY, here is data in correct format:-
let
|
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.
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
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.
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")
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?
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.
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:-
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
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.
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.
Table.TransformColumns(
your_table,
{"Telephone number (delivery)",
(x) => Text.PadStart(
Text.Combine(
Splitter.SplitTextByWhitespace()(x)
), 11, "0"
)}
)
Thank you. I will attempt to integrate this. The other solution sort of worked in isolation but wasn't 100%.
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.
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"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |