Forum Discussion

PatrickByGecko's avatar
PatrickByGecko
Helper V
2 years ago
Solved

Table.Distinct power query with a condition

Hello I speak about power queries on desktop power bi. Here is my table records [Field1 = "A", Field2 = "1", Field3= 20231201], [Field1 = "A", Field2 = "1", Field3= 20240101], [Field1 = "B", Fi...
Need Help
  • PatrickByGecko's avatar
    PatrickByGecko
    2 years ago

    Hi

    I Found by myself.

    The solutions proposed with grouping and expanding rows can't suppress duplicates fields.

    We have to

    • sort [COLLABORATEURId], [CODE PROJETId] [DATE FIN] on descending [DATE FIN] (because I want to keep only Max [DATE FIN] for duplicates [COLLABORATEURId], [CODE PROJETId]
    • and then do a Table.Distinct

    It works fine!

    🙂

PatrickByGecko's avatar
PatrickByGecko
Helper V
2 years ago

I have readen carrefully and did exactly this=>

= Table.Group(#"Colonnes renommées4", {"COLLABORATEURId", "CODE PROJETId"}, {{"COUPLE COLLAB PROJET", each List.Max([DATE FIN]), type datetime}})

And it works for Grouping.

But after there is nothing to expand by clicking on icons at top of column of [COUPLE COLLAB PROJET].

If I try to write directly  this =>

#"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"Field2", "Field3"}, {"Field2", "Field3"})

Do I have to put the name of all my 7 columns this way=>

#"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"Field2", "Field3","Field4","Field5","Field6","Field7" }, {"Field2", "Field3","Field4","Field5","Field6","Field7" })

?

Thanks.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

You obviously have not see my solution carefully at all.  Where have i used List.Max????  I have used Table.Max.  Also, there is no type datetime in the Table.Group step of my solution.  Please study my solution carefully.