SOLVED

How to find unique value and display the value based on the earliest date/time criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2366373%22%20slang%3D%22en-US%22%3EHow%20to%20find%20unique%20value%20and%20display%20the%20value%20based%20on%20the%20earliest%20date%2Ftime%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2366373%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20to%20find%20unique%20values%20from%20column%20A%20in%20a%20table%20generated%20via%20Power%20Query%2C%20and%20based%20on%20another%20column%20that%20displays%20date%2Ftime%20(in%20column%20B)%20find%20the%20earliest%20date%20and%20display%20the%20total%20from%20column%20C%20for%20each%20distinct%20unique%20value%20from%20A.%3C%2FP%3E%3CP%3EI%20tried%20different%20methods%20but%20wasn't%20able%20to%20succeed%20so%20I'd%20appreciate%20any%20help%20here.%3C%2FP%3E%3CP%3EPlease%20find%20attached%20sample%20with%20explanation.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2366373%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2366422%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20find%20unique%20value%20and%20display%20the%20value%20based%20on%20the%20earliest%20date%2Ftime%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2366422%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1024126%22%20target%3D%22_blank%22%3E%40ragomes1972%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20use%20Power%20Query%20the%20easiest%20way%20to%20generate%20second%20table%20by%20it.%3C%2FP%3E%0A%3CP%3E-%20sort%20by%20Sprint%20and%20Time%2C%20both%20ascending%3C%2FP%3E%0A%3CP%3E-%20add%20Index%20to%20fix%20table%20in%20memory%3C%2FP%3E%0A%3CP%3E-%20remove%20duplicates%20on%20sprints%3C%2FP%3E%0A%3CP%3E-%20remove%20other%20colums%3C%2FP%3E%0A%3CP%3E-%20load%20to%20sheet%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, 

I have to find unique values from column A in a table generated via Power Query, and based on another column that displays date/time (in column B) find the earliest date and display the total from column C for each distinct unique value from A.

I tried different methods but wasn't able to succeed so I'd appreciate any help here.

Please find attached sample with explanation.

Thanks!

1 Reply
best response confirmed by ragomes1972 (Occasional Contributor)
Solution

@ragomes1972 

If you use Power Query the easiest way to generate second table is by it.

- reference first query

- sort by Sprint and Time, both ascending

- add Index to fix table in memory

- remove duplicates on sprints

- remove other columns

- load to sheet

 

Please check attached.