Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gary07
Frequent Visitor

Compare Two Sheet and show the differenct only

Hi I have two sheet

 

Master

 

Last Save

 

I want to compare Master with Last Save and show rows in which data has changed

 

I have about 16 column

 

I want to see only the row has different value

 

 

Example Master File

 

gary07_2-1703082108073.png

 

 

Last Save File

 

gary07_1-1703079856642.png

 

 

6 REPLIES 6
AlienSx
Super User
Super User

Hi

 

I have two files can you explain

mls.jpg

Hi

 

Where to add 

advanced editor

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"

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors