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.

     

MT88's avatar
MT88
New Member
3 years ago

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"