Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear Community,
Good Day!
Aprreciate if can provide me some help on Power Query Editor. How can I filter the earliest date in order to overcome the duplicate consolID issues.
For example 'C00015706' , the earliest date is 23/1/2019. And I only need this row.
Here attached with the pbix: https://ufile.io/5c6q50oj
Any help will appreciated!
Solved! Go to Solution.
In advanced editor replace the code with this:
let
Source = Excel.Workbook(File.Contents("C:\Users\nikchoo\Desktop\testing123.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Consol ID", type text}, {"ETD", type date}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"ETD", Order.Ascending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Consol ID"})
in
#"Removed Duplicates"
The idea is simply sorting date in ascending order. Use the Table.Buffer to cache the sorting then remove duplicates.
Please check the sample file.
In advanced editor replace the code with this:
let
Source = Excel.Workbook(File.Contents("C:\Users\nikchoo\Desktop\testing123.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Consol ID", type text}, {"ETD", type date}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"ETD", Order.Ascending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Consol ID"})
in
#"Removed Duplicates"
The idea is simply sorting date in ascending order. Use the Table.Buffer to cache the sorting then remove duplicates.
Please check the sample file.
Hey @NickProp28 ,
personally I'm restricted to downloading files from onedrive, dropbox, or google drive only.
Nevertheless, a solution to your requirement should be straightforward.
Create a grouping transformation based on the column "Console ID". This article describes how to use this transformation: Grouping or summarizing rows | Microsoft Docs
Create a grouping that contains two aggregations
Then expand the column that holds all the columns, be aware that also the column Console ID is contained. Create a custom column that checks if the column ETD equals the column with the Minimum date return 1 if true, otherwise 0.
Finally, you can filter all the rows that contain 1.
Maybe things can be more simple if you just want to remove duplicates, by using the Remove Duplicates transformation:
Hopefully, this provides what you are looking for.
Regards,
Tom
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |