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", Field2 = "2", Field3= 20231201]

Is it possible to supress duplicates records on Field1 and Field2 with a condition on Field 3 asking for example to keep the newer date?

The result should be;

[Field1 = "A", Field2 = "1", Field3= 20240101],

[Field1 = "B", Field2 = "2", Field3= 20231201]

Is it possible with Table.Distinct or a kind of Power query like that?

Thanks!

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!

    🙂

10 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field1", type text}, {"Field2", Int64.Type}, {"Field3", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Field1"}, {{"All", each Table.Max(_,"Field3")}}),
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"Field2", "Field3"}, {"Field2", "Field3"})
in
    #"Expanded All"

    Hope this helps.

     

    • PatrickByGecko's avatar
      PatrickByGecko
      Helper V
      2 years ago

      Hi Ashish

      Thank you for you help. I succeeded to group. But It did not succeed on expanding all the other fields.

      I got this when grouping.

      How can i manage the Table.ExpandRecordColum so as to get back all the other fields?

      Thanks.

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        You are welcome.  You just have to click on the icon in the header and select the fields that you want to expand. 

  • 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. 

    • PatrickByGecko's avatar
      PatrickByGecko
      Helper V
      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!

      🙂