Compare Duplicate Row Contents

[Syndicate_Admin]

I have a spreadsheet that contains the change history of various documents.

It may happen that the title or the name of the document changes. However, the document number remains the same.

I'm looking for a way (in power query) to mark or see the documents where the name or title has changed.

The table has the following structure:

Row	Document number	Title	Name	Date of change
1	000.001	Abc	Xyz	01.01.2020
2	000.001	Abcd	Xyz	02.01.2020
3	000.002	Abc	Xyz	03.01.2021
4	000.002	Abc	Wxyz	02.01.2021
5	000.003	Abc	Xyz	02.02.2022
6	000.003	Abc	Xyz	02.03.2022

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

1.You can create a index column grouped by Document number field first,  You can refer to the following link.

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

2.Based on the fiest step, then create a custom column(the index column is created in the first step)

Table.SelectRows(#"Expanded Table",(x)=>x[Document number]=[Document number] and x[Index]=1)

3.Based on the first step, then create a custom column

if [Index]=1 then null else if List.Min([Custom][Title])<>[Title] then "Title Changed" else if List.Min([Custom][Name])<>[Name]  then "Name Changed" else if List.Min([Custom][Name])<>[Name] and List.Min([Custom][Title])<>[Title]  then "Name and title both changed" else "Not change"

Output

and you can refer to the following code as an sample, create a blank query and put it to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIwMNAzMACxHJOSgWREZRVI1FAPiIwMjAyUYnWilYzQFaYgVBqhqDSGqzRCN9IYqtAQrNAEi8LwClQjISpN4SqN0Y0EKjQCKTQCKzTDq9AYqjAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, #"Document number" = _t, Title = _t, Name = _t, #"Date of change" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"Document number", type number}, {"Title", type text}, {"Name", type text}, {"Date of change", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Document number"}, {{"Table", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"Row", "Title", "Name", "Date of change", "Index"}, {"Row", "Title", "Name", "Date of change", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table", "Custom", each Table.SelectRows(#"Expanded Table",(x)=>x[Document number]=[Document number] and x[Index]=1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Index]=1 then null else if List.Min([Custom][Title])<>[Title] then "Title Changed" else if List.Min([Custom][Name])<>[Name]  then "Name Changed" else if List.Min([Custom][Name])<>[Name] and List.Min([Custom][Title])<>[Title]  then "Name and title both changed" else "Not change")
/*if [Index]=1 then null else if[Custom][Title]<>[Title] then "Title Changed" else if [Custom][Name]<>[Name]  then "Name Changed" else if [Custom][Name]<>[Name] and [Custom][Title]<>[Title]  then "Name and title both changed" else "Not change")))*/
in
    #"Added Custom1"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Same code, just a different source.  Maybe use Table.Buffer but it may not bring much.

[Syndicate_Admin]

Hi Ibendlin,

Okay. What would the code look like if I wanted to use it for another dataset with an extremely large number of rows?

Thank you for your help.

[Syndicate_Admin]

That is your sample data, encoded in Base64 and zipped up.

[Syndicate_Admin]

I'm curious. What does the long string of numbers at the beginning of the code mean or do? i45WM...

[Syndicate_Admin]

This can be done in a variety of ways. Here's one.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIwMNAzMACxHJOSgWREZRVI1FAPiIwMjAyUYnWilYzQFaYgVBqhqDSGqzRCN9IYqtAQrNAEi8LwClQjISpN4SqN0Y0EKjQCKTQCKzTDq9AYqjAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, #"Document number" = _t, Title = _t, Name = _t, #"Date of change" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"Document number", type text}, {"Title", type text}, {"Name", type text}, {"Date of change", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Document number"}, {{"Min Title", each List.Min([Title]), type nullable text}, {"Max Title", each List.Max([Title]), type nullable text}, {"Min Name", each List.Min([Name]), type nullable text}, {"Max Name", each List.Max([Name]), type nullable text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Changed", each [Min Title]<>[Max Title] or [Min Name]<>[Max Name]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Document number", "Changed"})
in
    #"Removed Other Columns"