Forum Discussion

MT88's avatar
MT88
New Member
3 years ago
Solved

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 ...
Need Help
  • AlB's avatar
    AlB
    3 years ago

    MT88 

    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.

     

AlB's avatar
AlB
Community Champion
3 years ago

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.