Table.Distinct power query with a condition

[PatrickByGecko]

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!

[PatrickByGecko]

Can somebody help please?

[PatrickByGecko · Accepted solution]

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]

And please don't forget I'm using Power BI Desktop not excel.

[PatrickByGecko]

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]

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. 

[Ashish_Mathur]

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]

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]

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

[PatrickByGecko]

Hi Ashish

There is nothing to expand when i click these ways, have a look =>

[Ashish_Mathur]

I cannot help you by looking at screenshots.  Read my solution carefully again and retry.