Forum Discussion
Unable to get this result when I copy code into another query! Code here is in PROPER format!!
Hi all Finally worked out how to provide sample data. Basically, I started a new example and the code below gives me the result I want. However, when I copy code into another query, it does...
Would someone be so kind as to advise me how to tidy this code up please? I've got reordered and renamed columns all over the place. It would be nice to tidy up the applied steps!
I cannot post the actual file but would simply like to consolidate the lines. Is this even possible? I read some articles that say it is but the examples shown looked way too complex for my abilities!
| let Source = Csv.Document(File.Contents("C:\Users\roger\Downloads\orders.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)", type text}, {"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}, {"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", type text}, {"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", type text}, {"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}, {"Safe place", type text}, {"Additions", type text}, {"Product list", Int64.Type}, {"Product full", Int64.Type}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Telephone number (delivery)"}), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","+","00",Replacer.ReplaceText,{"Telephone number (delivery)"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value2",{{"Full name (delivery)", "Recipient Name"}, {"Company name (delivery)", "Recipient Business Name"}, {"Address line 1 (delivery)", "Recipient Address Line 1"}, {"Address line 2 (delivery)", "Recipient Address Line 2"}, {"County (delivery)", "Recipient Address Line 3 (County)"}, {"Town / city (delivery)", "Recipient Town"}, {"Postcode (delivery)", "Recipient Postcode"}, {"Telephone number (delivery)", "Recipient Mobile Phone"}, {"Email address", "Recipient Email Address"}, {"Shipping Weight", "Weight"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Product title", "Payment total", "Recipient Business Name", "Recipient Name", "Recipient Address Line 1", "Recipient Address Line 2", "Recipient Address Line 3 (County)", "Recipient Postcode", "Recipient Town", "Country (delivery)", "Country code (delivery)", "Recipient Mobile Phone", "Recipient Email Address", "Safe place", "Order #", "Weight", "Length", "Width", "Height", "Channel", "Channel order #", "Invoice #", "Order date", "Invoice date", "Due date", "Delivery date", "Despatch date", "Status", "Custom status", "Paid", "Paid on", "Placed by", "Completed on", "Completed by", "Allow contact", "User group", "Account ID", "Title", "First name", "Last name", "Full title", "Full name", "Company name", "Address line 1", "Address line 2", "Town / city", "County", "Postcode", "Country", "Country code", "Telephone number", "VAT number", "EORI number", "Facebook ID", "Delivery address", "Title (delivery)", "First name (delivery)", "Last name (delivery)", "Full title (delivery)", "Currency code", "VAT rate", "Payment method", "Payment ID", "Voucher code", "Voucher total", "Product lines", "Product SKU", "Product parent", "Product bar code", "Product part number", "Product variant", "Product category", "Product brand", "Product supplier", "Product drop shipper", "Product intangible", "Product file", "Product file group", "Product bundle", "Product bundled", "Product gift voucher", "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", "IP address", "Device type", "Site version", "Affiliate", "Referring domain", "Referring search", "Customer comments", "Admin comments", "Additions", "Product list", "Product full"}), #"Uppercased Text" = Table.TransformColumns(#"Reordered Columns",{{"Recipient Town", Text.Upper, type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Uppercased Text",{{"Order #", "Reference Number"}}), #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Recipient Business Name", "Recipient Name", "Recipient Address Line 1", "Recipient Address Line 2", "Recipient Address Line 3 (County)", "Recipient Postcode", "Recipient Town", "Country (delivery)", "Country code (delivery)", "Recipient Mobile Phone", "Recipient Email Address", "Safe place", "Reference Number", "Weight", "Length", "Width", "Height", "Channel", "Channel order #", "Invoice #", "Order date", "Invoice date", "Due date", "Delivery date", "Despatch date", "Status", "Custom status", "Paid", "Paid on", "Placed by", "Completed on", "Completed by", "Allow contact", "User group", "Account ID", "Title", "First name", "Last name", "Full title", "Full name", "Company name", "Address line 1", "Address line 2", "Town / city", "County", "Postcode", "Country", "Country code", "Telephone number", "VAT number", "EORI number", "Facebook ID", "Delivery address", "Title (delivery)", "First name (delivery)", "Last name (delivery)", "Full title (delivery)", "Currency code", "VAT rate", "Payment method", "Payment ID", "Voucher code", "Voucher total", "Product lines", "Product SKU", "Product parent", "Product bar code", "Product part number", "Product variant", "Product category", "Product brand", "Product supplier", "Product drop shipper", "Product intangible", "Product file", "Product file group", "Product bundle", "Product bundled", "Product gift voucher", "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", "IP address", "Device type", "Site version", "Affiliate", "Referring domain", "Referring search", "Customer comments", "Admin comments", "Additions", "Product list", "Product full", "Product title", "Payment total"}), #"Split Column by Position" = Table.SplitColumn(#"Reordered Columns1", "Safe place", Splitter.SplitTextByRepeatedLengths(25), {"Safe place.1", "Safe place.2", "Safe place.3", "Safe place.4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Safe place.1", type text}, {"Safe place.2", type text}, {"Safe place.3", type text}, {"Safe place.4", type text}}), #"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Safe place.1", "Special Instructions 1"}, {"Safe place.2", "Special Instructions 2"}, {"Safe place.3", "Special Instructions 3"}, {"Safe place.4", "Special Instructions 4"}}), #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns2", "Enhanced Compensation", each if [Payment total] <= 100 then "JCM0" else if [Payment total] <= 500 then "JCM1" else if [Payment total] <= 1000 then "JCM2" else if [Payment total] <= 1500 then "JCM3" else if [Payment total] <= 2000 then "JCM4" else if [Payment total] <= 2500 then "JCM5" else "JCM5"), #"Reordered Columns2" = Table.ReorderColumns(#"Added Conditional Column",{"Recipient Business Name", "Recipient Name", "Recipient Address Line 1", "Recipient Address Line 2", "Recipient Address Line 3 (County)", "Recipient Postcode", "Recipient Town", "Country (delivery)", "Country code (delivery)", "Recipient Mobile Phone", "Recipient Email Address", "Special Instructions 1", "Special Instructions 2", "Special Instructions 3", "Special Instructions 4", "Reference Number", "Enhanced Compensation", "Weight", "Length", "Width", "Height", "Channel", "Channel order #", "Invoice #", "Order date", "Invoice date", "Due date", "Delivery date", "Despatch date", "Status", "Custom status", "Paid", "Paid on", "Placed by", "Completed on", "Completed by", "Allow contact", "User group", "Account ID", "Title", "First name", "Last name", "Full title", "Full name", "Company name", "Address line 1", "Address line 2", "Town / city", "County", "Postcode", "Country", "Country code", "Telephone number", "VAT number", "EORI number", "Facebook ID", "Delivery address", "Title (delivery)", "First name (delivery)", "Last name (delivery)", "Full title (delivery)", "Currency code", "VAT rate", "Payment method", "Payment ID", "Voucher code", "Voucher total", "Product lines", "Product SKU", "Product parent", "Product bar code", "Product part number", "Product variant", "Product category", "Product brand", "Product supplier", "Product drop shipper", "Product intangible", "Product file", "Product file group", "Product bundle", "Product bundled", "Product gift voucher", "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", "IP address", "Device type", "Site version", "Affiliate", "Referring domain", "Referring search", "Customer comments", "Admin comments", "Additions", "Product list", "Product full", "Product title", "Payment total"}), #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns2",{{"Recipient Business Name", "Company name (delivery)"}}), #"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns3","",null,Replacer.ReplaceValue,{"Company name (delivery)"}), #"Reordered Columns3" = Table.ReorderColumns(#"Replaced Value1",{"Recipient Name", "Recipient Address Line 1", "Recipient Address Line 2", "Recipient Address Line 3 (County)", "Recipient Postcode", "Recipient Town", "Country (delivery)", "Country code (delivery)", "Recipient Mobile Phone", "Recipient Email Address", "Special Instructions 1", "Special Instructions 2", "Special Instructions 3", "Special Instructions 4", "Reference Number", "Enhanced Compensation", "Weight", "Length", "Width", "Height", "Channel", "Channel order #", "Invoice #", "Order date", "Invoice date", "Due date", "Delivery date", "Despatch date", "Status", "Custom status", "Paid", "Paid on", "Placed by", "Completed on", "Completed by", "Allow contact", "User group", "Account ID", "Title", "First name", "Last name", "Full title", "Full name", "Company name", "Address line 1", "Address line 2", "Town / city", "County", "Postcode", "Country", "Country code", "Telephone number", "VAT number", "EORI number", "Facebook ID", "Delivery address", "Company name (delivery)", "Title (delivery)", "First name (delivery)", "Last name (delivery)", "Full title (delivery)", "Currency code", "VAT rate", "Payment method", "Payment ID", "Voucher code", "Voucher total", "Product lines", "Product SKU", "Product parent", "Product bar code", "Product part number", "Product variant", "Product category", "Product brand", "Product supplier", "Product drop shipper", "Product intangible", "Product file", "Product file group", "Product bundle", "Product bundled", "Product gift voucher", "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", "IP address", "Device type", "Site version", "Affiliate", "Referring domain", "Referring search", "Customer comments", "Admin comments", "Additions", "Product list", "Product full", "Product title", "Payment total"}), #"Added Conditional Column1" = Table.AddColumn(#"Reordered Columns3", "Recipient Business Name", each if [#"Company name (delivery)"] = null then [Recipient Name] else [#"Company name (delivery)"]), #"Reordered Columns4" = Table.ReorderColumns(#"Added Conditional Column1",{"Recipient Business Name", "Recipient Name", "Recipient Address Line 1", "Recipient Address Line 2", "Recipient Address Line 3 (County)", "Recipient Postcode", "Recipient Town", "Recipient Mobile Phone", "Recipient Email Address", "Special Instructions 1", "Special Instructions 2", "Special Instructions 3", "Special Instructions 4", "Reference Number", "Enhanced Compensation", "Weight", "Length", "Width", "Height", "Channel", "Channel order #", "Invoice #", "Order date", "Invoice date", "Due date", "Delivery date", "Despatch date", "Status", "Custom status", "Paid", "Paid on", "Placed by", "Completed on", "Completed by", "Allow contact", "User group", "Account ID", "Title", "First name", "Last name", "Full title", "Full name", "Company name", "Address line 1", "Address line 2", "Town / city", "County", "Postcode", "Country", "Country code", "Telephone number", "VAT number", "EORI number", "Facebook ID", "Delivery address", "Company name (delivery)", "Title (delivery)", "First name (delivery)", "Last name (delivery)", "Full title (delivery)", "Country (delivery)", "Country code (delivery)", "Currency code", "VAT rate", "Payment method", "Payment ID", "Voucher code", "Voucher total", "Product lines", "Product SKU", "Product parent", "Product bar code", "Product part number", "Product variant", "Product category", "Product brand", "Product supplier", "Product drop shipper", "Product intangible", "Product file", "Product file group", "Product bundle", "Product bundled", "Product gift voucher", "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", "IP address", "Device type", "Site version", "Affiliate", "Referring domain", "Referring search", "Customer comments", "Admin comments", "Additions", "Product list", "Product full", "Product title", "Payment total"}) in #"Reordered Columns4" |
Like you mentioned - we do not see the data. I just delete some part of code and edited few steps. Try this:
Tip: I think that you do not need all columns. Instead of reordering them you can hold CTRL then start clicking to column headers continuously. When you select all wanted columns, right click on one of selected column header and choose REMOVE OTHER COLUMNS option. This will remove non-selected columns and reorder them the way you were choosing them.
let
Source = Csv.Document(File.Contents("C:\Users\roger\Downloads\orders.csv"),[Delimiter=",", Columns=118, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers"," ","",Replacer.ReplaceText,{"Telephone number (delivery)"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","+","00",Replacer.ReplaceText,{"Telephone number (delivery)"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value2",{{"Full name (delivery)", "Recipient Name"}, {"Address line 1 (delivery)", "Recipient Address Line 1"}, {"Address line 2 (delivery)", "Recipient Address Line 2"}, {"County (delivery)", "Recipient Address Line 3 (County)"}, {"Town / city (delivery)", "Recipient Town"}, {"Postcode (delivery)", "Recipient Postcode"}, {"Telephone number (delivery)", "Recipient Mobile Phone"}, {"Email address", "Recipient Email Address"}, {"Shipping Weight", "Weight"}, {"Order #", "Reference Number"}}),
#"Uppercased Text" = Table.TransformColumns(#"Renamed Columns",{{"Recipient Town", Text.Upper, type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Uppercased Text", "Safe place", Splitter.SplitTextByRepeatedLengths(25), {"Special Instructions 1", "Special Instructions 2", "Special Instructions 3", "Special Instructions 4"}),
#"Added Conditional Column" = Table.AddColumn(#"Split Column by Position", "Enhanced Compensation", each if [Payment total] <= 100 then "JCM0" else if [Payment total] <= 500 then "JCM1" else if [Payment total] <= 1000 then "JCM2" else if [Payment total] <= 1500 then "JCM3" else if [Payment total] <= 2000 then "JCM4" else if [Payment total] <= 2500 then "JCM5" else "JCM5"),
#"Replaced Value3" = Table.ReplaceValue(#"Added Conditional Column","",null,Replacer.ReplaceValue,{"Company name (delivery)"}),
#"Added Conditional Column1" = Table.AddColumn(#"Replaced Value3", "Recipient Business Name", each if [#"Company name (delivery)"] = null then [Recipient Name] else [#"Company name (delivery)"]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column1",{"Recipient Business Name", "Recipient Name", "Recipient Address Line 1", "Recipient Address Line 2", "Recipient Address Line 3 (County)", "Recipient Postcode", "Recipient Town", "Recipient Mobile Phone", "Recipient Email Address", "Special Instructions 1", "Special Instructions 2", "Special Instructions 3", "Special Instructions 4", "Reference Number", "Enhanced Compensation", "Weight", "Length", "Width", "Height", "Channel", "Channel order #", "Invoice #", "Order date", "Invoice date", "Due date", "Delivery date", "Despatch date", "Status", "Custom status", "Paid", "Paid on", "Placed by", "Completed on", "Completed by", "Allow contact", "User group", "Account ID", "Title", "First name", "Last name", "Full title", "Full name", "Company name", "Address line 1", "Address line 2", "Town / city", "County", "Postcode", "Country", "Country code", "Telephone number", "VAT number", "EORI number", "Facebook ID", "Delivery address", "Company name (delivery)", "Title (delivery)", "First name (delivery)", "Last name (delivery)", "Full title (delivery)", "Country (delivery)", "Country code (delivery)", "Currency code", "VAT rate", "Payment method", "Payment ID", "Voucher code", "Voucher total", "Product lines", "Product SKU", "Product parent", "Product bar code", "Product part number", "Product variant", "Product category", "Product brand", "Product supplier", "Product drop shipper", "Product intangible", "Product file", "Product file group", "Product bundle", "Product bundled", "Product gift voucher", "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", "IP address", "Device type", "Site version", "Affiliate", "Referring domain", "Referring search", "Customer comments", "Admin comments", "Additions", "Product list", "Product full", "Product title", "Payment total"})
in
#"Reordered Columns"
WOW! That's such a great tip.
I've found it quite labourious to reorder.
You are right I do not need all columns.
I am slowly but surely gaining confidence now. Just this morning I was going to post another problem as I got an expression error
But instead I looked at the code and tried to figure out what was wrong.
This line had the number 5 in it so I changed it to 1 and retested and it resolved the error then I changed it to 0.= #"Added Custom1"{0}[Custom]
You don't know how happy this makes me to actually be learning stuff like this at my age!
You are an absolute diamond helping people out as you do. Everyone on here is just great.
I glad I can self-help a little now and I can only grow I think.
Last night I broke down the two queries to split between two worksheets, one for box sizes over 300 cm girth and one for box sizes below. That in itself is life changing in simplifying my workload and ensuring errors are not made.
So thanks again and apologies for being so dumb the other week in not being able to work out how to post sample data properly even.
I hope I finally got it right this time!
Very best wishes.