Forum Discussion
Compare Duplicate Row Contents
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 |
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.
6 Replies
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"I'm curious. What does the long string of numbers at the beginning of the code mean or do? i45WM...
- That is your sample data, encoded in Base64 and zipped up.
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.
Same code, just a different source. Maybe use Table.Buffer but it may not bring much.
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.
