Forum Discussion
Compare rows in different columns
Hi @ everyone,
I have a table with dublicates in two different columns and a third column which got a date.
I want to compare the rows in the first two columns (where the duplicates are) and if the next row contains the same information as the row before, the script should keep the row with the newer date and delete the row with the older date.
Example:
The script would see that row 2 got the same information as row 1 = Name and the Course is a duplicate,
so next it looks at the column "Date" and keep the row with the newer date = "11.01.2022" and delete the row with the older date = "19.07.2021".
| Name | Course | Date |
| Mr T | Basketball | 11.01.2022 |
| Mr T | Basketball | 19.07.2021 |
| Mrs L | Golf | 10.05.2022 |
| Mr M | Football | 20.12.2021 |
Can anyone help me ? I couldnt find a topic with the same problem.
You need to read the table in correctly in Excel. Assuming the data is formatted as table and named "Tabelle3", as in the xlsx you have shared:
let Source = Excel.CurrentWorkbook(), Tabelle3 = Source{[Name="Tabelle3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Tabelle3,{{"Mitarbeiter", type text}, {"Kurs", type text}, {"Ablaufdatum", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Mitarbeiter", "Kurs"}, {{"Ablaufdatum", each List.Max([Ablaufdatum]), type date}}) in #"Grouped Rows"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
8 Replies
Hi MT88
Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i1SCFHSUXJKLM5OLUlKzMkBcgwN9QwM9YwMjIyUYnVwKLHUMzAHKTGEKilW8AEKu+fnpIFkDfQMTFEM8AWKuuXnw7QbGegZGkG1xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Course = _t, Date = _t]), #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Name", type text}, {"Course", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Course", Order.Ascending}, {"Date", Order.Descending}}), #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Name", "Course"}) in #"Removed Duplicates"It first sorts rows, so that the earlier dates stay on top, then removes duplicates based on Name-Course. The duplicate kept will be the one appearing first in the table (from top to bottom)
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
You need to read the table in correctly in Excel. Assuming the data is formatted as table and named "Tabelle3", as in the xlsx you have shared:
let Source = Excel.CurrentWorkbook(), Tabelle3 = Source{[Name="Tabelle3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Tabelle3,{{"Mitarbeiter", type text}, {"Kurs", type text}, {"Ablaufdatum", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Mitarbeiter", "Kurs"}, {{"Ablaufdatum", each List.Max([Ablaufdatum]), type date}}) in #"Grouped Rows"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
I did it (in excel) ! It was the name of the table that was wrong in my code -.-
And in Power Bi Power Query the last error was a syntax error -> comma
Finally found it then it worked.
THANK YOU !
Another option is to group by Name and Course and select the max date as the operation:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i1SCFHSUXJKLM5OLUlKzMkBcgwN9QwM9YwMjIyUYnVwKLHUMzAHKTGEKilW8AEKu+fnpIFkDfQMTFEM8AWKuuXnw7QbGegZGkG1xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Course = _t, Date = _t]), #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Name", type text}, {"Course", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Course"}, {{"Count", each List.Max([Date]), type nullable date}}) in #"Grouped Rows"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
First : Thanks for that solution ! 🙂
It works for the example but not for the real table.
Can you explain me how I convert the script for the real table ?
I tried to rebuild it for my table but it ends with an expression error.
A value of type "Table" can not be convert in type "List"
Details:
Value = [Table]
Type = [Type]
Name of Table / Source = Ablaufende Kurse
Table is an excel sheet.
Names of Columns:
Name = Mitarbeiter
Course = Kurs
Date = Ablaufdatum
Heres a copy of my code:
let
Source = Table.FromRows(#"Ablaufende Kurse", let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mitarbeiter = _t, Kurs = _t, Ablaufdatum = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Ablaufdatum", type date}}, "en-GB"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Mitarbeiter", type text}, {"Kurs", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Mitarbeiter", "Kurs"}, {{"Count", each List.Max([Ablaufdatum]), type nullable date}})
in
#"Grouped Rows"I'd need a sample of #"Ablaufende Kurse" to be able to reproduce the issue
You might not need these two steps:
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Ablaufdatum", type date}}, "en-GB"), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Mitarbeiter", type text}, {"Kurs", type text}}),Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
It works with no issues on my side. Note the only relevant step below is the last one: #"Grouped Rows"
The previous ones are just to get the data in
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUXJKLM5OLUlKzMkBcowMdf2TS3SNjJRidbBJGyBLR2FIG+t6lebApCPRpQ0NdR1L03WNDMHSjujSBua6LqnJMN1OGIYbIduNKY3icmd0aWMD3eDUApjdLhh2W8KdFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mitarbeiter = _t, Kurs = _t, Ablaufdatum = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mitarbeiter", type text}, {"Kurs", type text}, {"Ablaufdatum", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Mitarbeiter", "Kurs"}, {{"Ablaufdatum", each List.Max([Ablaufdatum]), type nullable date}}) in #"Grouped Rows"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
When I copy the code, I always get the result for the sample or previous case, but not from my table I actually loaded. When I want the to change the source, its gets me the previous result or an error.
So the code works but not for my actually source its shows only the result for the sample.
My Code (my Problem is to link it to the actually table / source):
let
Source = Excel.CurrentWorkbook#"Grouped Rows" = Table.Group(#"Changed Type", {"Mitarbeiter", "Kurs"}, {{"Ablaufdatum", each List.Max([Ablaufdatum]), type nullable date}})
in
#"Grouped Rows"I also tried (in Power Bi / Power Query):
let
Source = Table.FromRows #"Ablaufende Kurse"
#"Grouped Rows" = Table.Group(#"Changed Type", {"Mitarbeiter", "Kurs"}, {{"Ablaufdatum", each List.Max([Ablaufdatum]), type nullable date}})
in
#"Grouped Rows"
