- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!
🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Can somebody help please?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!
🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

And please don't forget I'm using Power BI Desktop not excel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Ashish
There is nothing to expand when i click these ways, have a look =>
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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/

Helpful resources
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.

User | Count |
---|---|
71 | |
69 | |
38 | |
26 | |
26 |
User | Count |
---|---|
97 | |
96 | |
59 | |
44 | |
40 |