Dynamically filter duplicates

[adhossain]

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?

[Payeras_BI · Accepted solution]

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)

[adhossain]

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.

Excel data sheet 

Power Bi report

[Payeras_BI]

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.

[adhossain]

Thank you very much. Yes, that is exactly what I was looking for. I forgot to drag Status (measure) on the table.

Thank you again

[Payeras_BI]

I'm glad I could assist you. 🙂