Forum Discussion
Dynamically filter duplicates
I ran into a strange situation where I have an excel table like below;
| Activity | Unique ID of Activity | # Achieved | Reporting Month | Status |
| Training 1 | ID01 | 20 | 2023-01 | Ongoing |
| Orientation 1 | ID02 | 26 | 2023-01 | Ongoing |
| Distribution 1 | ID03 | 25 | 2023-01 | Ongoing |
| Training 1 | ID01 | 21 | 2023-02 | Ongoing |
| Orientation 1 | ID02 | 69 | 2023-02 | Ongoing |
| Distribution 1 | ID03 | 30 | 2023-02 | Ongoing |
| Training 1 | ID01 | 33 | 2023-03 | Completed |
| Orientation 1 | ID02 | 83 | 2023-03 | Ongoing |
| Distribution 1 | ID03 | 74 | 2023-03 | Ongoing |
| Training 2 | ID04 | 39 | 2023-04 | Ongoing |
| Orientation 1 | ID02 | 25 | 2023-04 | Completed |
| Distribution 1 | ID03 | 96 | 2023-04 | Ongoing |
| Training 2 | ID04 | 33 | 2023-05 | Ongoing |
| Orientation 2 | ID05 | 41 | 2023-05 | Ongoing |
| Distribution 1 | ID03 | 82 | 2023-05 | Completed |
| Training 2 | ID04 | 72 | 2023-06 | Ongoing |
| Orientation 2 | ID05 | 36 | 2023-06 | Completed |
| Distribution 2 | ID06 | 85 | 2023-06 | Ongoing |
I am able to remove duplicates based on "Unique ID of Activity" column where I want to keep the items which are "Completed" only but remove the "Ongoing" duplicate entries. I used "Table.Buffer" method. My query M code is below;
let
Source = Excel.Workbook(File.Contents("C:\test\Activity_Tracker.xlsx"), null, true),
Acticity_Table_Table = Source{[Item="Acticity_Table",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Acticity_Table_Table,{{"Activity", type text}, {"Unique ID of Activity", type text}, {"# Achieved", Int64.Type}, {"Reporting Month", type date}, {"Status", type text}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Status", Order.Ascending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Unique ID of Activity"})
in
#"Removed Duplicates"
This all works fine and returns a similar table like below which is very obvious to happen.
But I want a differnt result. As you can see, though my dataset starts from 2023-01 (or January 2023 in reporting Month column), I am not able to see what happened during that time as "Remove Duplicates" command removed those rows. How can I be able to see what happened in first 1 month, or first two months or first three months.
What could be a solution to this scenario?
Hi adhossain ,
I don't think you want to remove duplicates in Power Query if later you want to be able to visualize those rows in your matrix.
I would load everything instead and control what is shown, through measures, like this:
Measures:
_# Achieved = VAR maxReportingMonth = CALCULATE(MAX('Table'[Reporting Month]),ALLSELECTED('Table'[Reporting Month])) RETURN CALCULATE(SELECTEDVALUE('Table'[# Achieved]),'Table'[Reporting Month]= maxReportingMonth) -------------------- _# Reporting Month = VAR maxReportingMonth = CALCULATE(MAX('Table'[Reporting Month]),ALLSELECTED('Table'[Reporting Month])) RETURN maxReportingMonth -------------------- _Status = VAR maxReportingMonth = CALCULATE(MAX('Table'[Reporting Month]),ALLSELECTED('Table'[Reporting Month])) RETURN CALCULATE(SELECTEDVALUE('Table'[Status]),'Table'[Reporting Month]= maxReportingMonth)
5 Replies
Hi adhossain ,
I don't think you want to remove duplicates in Power Query if later you want to be able to visualize those rows in your matrix.
I would load everything instead and control what is shown, through measures, like this:
Measures:
_# Achieved = VAR maxReportingMonth = CALCULATE(MAX('Table'[Reporting Month]),ALLSELECTED('Table'[Reporting Month])) RETURN CALCULATE(SELECTEDVALUE('Table'[# Achieved]),'Table'[Reporting Month]= maxReportingMonth) -------------------- _# Reporting Month = VAR maxReportingMonth = CALCULATE(MAX('Table'[Reporting Month]),ALLSELECTED('Table'[Reporting Month])) RETURN maxReportingMonth -------------------- _Status = VAR maxReportingMonth = CALCULATE(MAX('Table'[Reporting Month]),ALLSELECTED('Table'[Reporting Month])) RETURN CALCULATE(SELECTEDVALUE('Table'[Status]),'Table'[Reporting Month]= maxReportingMonth)Hi Payeras_BI
At first thank you for your suggestions. But this returns entries of duplicate items. I only want to return the values from recent dates only fro the same Unique IDs. Kindly see below HIGHLIGHTED rows which I only want to retrieve.
- For first three months
- For first four months
- For first 5 months
- For first six months
Thanks in advanced.
Attaching the sample files if you want to exercise.
Hi adhossain ,
I have checked your file and seen that you have written the measures but you failed to drag on of them to the table visualization.
Please substitute the Status field by the status measure and come back to me with the result.
