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

MT88 

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.

 

  • MT88's avatar
    MT88
    New Member
    3 years ago

    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"