Reply
Partially syndicated - Outbound

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!

1 ACCEPTED SOLUTION

Syndicated - Outbound

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!

🙂

View solution in original post

10 REPLIES 10

Syndicated - Outbound

Can somebody help please?

Syndicated - Outbound

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!

🙂

Syndicated - Outbound

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

Syndicated - Outbound

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.

Syndicated - Outbound

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Syndicated - Outbound

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.

Ashish_Mathur_0-1701473970820.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

Hi Ashish

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

Screen1PBI.jpg

I got this when grouping.

Screen2PBI.jpg

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

Thanks.

 

Syndicated - Outbound

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

Hi Ashish

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

Screen1PBI.jpg

Screen2PBI.jpg

Syndicated - Outbound

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)