User Profile
gary07
Frequent Visitor
Joined 2 years ago
User Widgets
Contributions
Re: Compare Two Sheet and show the differenct only
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"
