Hi,
I have below can you advise where to add
Local file name is COPY CIH IMPORT LAST UPDATE.csv
let
Source = Csv.Document(Web.Contents("http://123.452.647:91/feeds/shopify_cost_import.csv"),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Variant SKU", Int64.Type}, {"Vendor", type text}, {"Metafield: product.modelno [single_line_text_field]", type text}, {"Variant Barcode", type text}, {"Metafield: supplier [multi_line_text_field]", type text}, {"Metafield: delivery_charge [number_integer]", Int64.Type}, {"Variant Cost", type number}, {"Metafield: product.agency [single_line_text_field]", type text}, {"Metafield: product_expired [string]", type text}, {"Type", type text}, {"Variant Requires Shipping", type logical}, {"Variant Command", type text}, {"Auto Book Delivery Standard Engineer", type text}, {"Auto Book Delivery Expedited Engineer", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Metafield: supplier [multi_line_text_field]"] = "CIH")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Vendor", Order.Ascending}}),
#"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Metafield: product.modelno [single_line_text_field]"}, Euronics, {"Model"}, "Euronics", JoinKind.LeftOuter),
#"Expanded Euronics" = Table.ExpandTableColumn(#"Merged Queries", "Euronics", {"Tags", "Warranty"}, {"Euronics.Tags", "Euronics.Warranty"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded Euronics",{{"Euronics.Warranty", "Metafield: manufacturer-warranty [single_line_text_field]"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns2",null,"",Replacer.ReplaceValue,{"Metafield: manufacturer-warranty [single_line_text_field]"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Variant Cost", "Variant Cost1"}}),
#"Variant Cost" = Table.AddColumn(#"Renamed Columns", "Variant Cost", each [Variant Cost1]+[#"Metafield: delivery_charge [number_integer]"]),
#"Reordered Columns" = Table.ReorderColumns(#"Variant Cost",{"Variant SKU", "Vendor", "Metafield: product.modelno [single_line_text_field]", "Variant Barcode", "Metafield: supplier [multi_line_text_field]", "Metafield: delivery_charge [number_integer]", "Variant Cost1", "Variant Cost", "Metafield: product.agency [single_line_text_field]", "Metafield: product_expired [string]", "Type", "Variant Requires Shipping", "Variant Command", "Auto Book Delivery Standard Engineer", "Auto Book Delivery Expedited Engineer", "Euronics.Tags"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Variant Cost1", "Auto Book Delivery Expedited Engineer"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Variant SKU", "Vendor", "Metafield: product.modelno [single_line_text_field]", "Variant Barcode", "Metafield: supplier [multi_line_text_field]", "Metafield: delivery_charge [number_integer]", "Variant Cost", "Metafield: product.agency [single_line_text_field]", "Metafield: product_expired [string]", "Type", "Euronics.Tags", "Variant Requires Shipping", "Variant Command", "Auto Book Delivery Standard Engineer"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Euronics.Tags", "Tags"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",null,"",Replacer.ReplaceValue,{"Tags"}),
#"Tags Command" = Table.AddColumn(#"Replaced Value", "Tags Command", each if [Variant Requires Shipping] = true then "MERGE" else "MERGE"),
#"Reordered Columns2" = Table.ReorderColumns(#"Tags Command",{"Variant SKU", "Vendor", "Metafield: product.modelno [single_line_text_field]", "Variant Barcode", "Metafield: supplier [multi_line_text_field]", "Metafield: delivery_charge [number_integer]", "Variant Cost", "Metafield: product.agency [single_line_text_field]", "Metafield: product_expired [string]", "Type", "Tags", "Tags Command", "Variant Requires Shipping", "Variant Command", "Auto Book Delivery Standard Engineer"}),
#"Metafield: products.courier [multi_line_text_field]" = Table.AddColumn(#"Reordered Columns2", "Metafield: products.courier [multi_line_text_field]", each if Text.Contains([Auto Book Delivery Standard Engineer], "Panther") then "Panther" else if Text.StartsWith([Auto Book Delivery Standard Engineer], "RM") then "Royal Mail" else if Text.StartsWith([Auto Book Delivery Standard Engineer], "ILINK") then "Interlink" else if Text.StartsWith([Auto Book Delivery Standard Engineer], "DPD") then "Interlink" else null),
#"Replaced Value2" = Table.ReplaceValue(#"Metafield: products.courier [multi_line_text_field]",null,"",Replacer.ReplaceValue,{"Metafield: products.courier [multi_line_text_field]"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value2",{"Auto Book Delivery Standard Engineer"}),
#"Reordered Columns3" = Table.ReorderColumns(#"Removed Columns1",{"Variant SKU", "Vendor", "Metafield: product.modelno [single_line_text_field]", "Variant Barcode", "Metafield: supplier [multi_line_text_field]", "Metafield: delivery_charge [number_integer]", "Variant Cost", "Metafield: product.agency [single_line_text_field]", "Metafield: product_expired [string]", "Type", "Tags", "Tags Command", "Variant Requires Shipping", "Metafield: manufacturer-warranty [single_line_text_field]", "Metafield: products.courier [multi_line_text_field]", "Variant Command"}),
#"Variant Taxable" = Table.AddColumn(#"Reordered Columns3", "Variant Taxable", each if [Variant Requires Shipping] = true then true else true),
#"Reordered Columns4" = Table.ReorderColumns(#"Variant Taxable",{"Variant SKU", "Vendor", "Metafield: product.modelno [single_line_text_field]", "Variant Barcode", "Metafield: supplier [multi_line_text_field]", "Metafield: delivery_charge [number_integer]", "Variant Cost", "Metafield: product.agency [single_line_text_field]", "Metafield: product_expired [string]", "Type", "Tags", "Tags Command", "Variant Requires Shipping", "Metafield: manufacturer-warranty [single_line_text_field]", "Metafield: products.courier [multi_line_text_field]", "Variant Taxable", "Variant Command"}),
#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns4",{"Vendor"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"Metafield: supplier [multi_line_text_field]", "Metafield: supplier [single_line_text_field]"}, {"Metafield: product_expired [string]", "Metafield: product_expired [single_line_text_field]"}}),
#"Sorted Rows1" = Table.Sort(#"Renamed Columns3",{{"Variant SKU", Order.Ascending}})
in
#"Sorted Rows1"
