Forum Discussion
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...
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!
🙂
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.
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.
You are welcome. You just have to click on the icon in the header and select the fields that you want to expand.
Hi Ashish
There is nothing to expand when i click these ways, have a look =>