Excel Forum

Flag First Instance of a value in one column as it relates to the running dates in another column.

L D

New Member

Members

Forum Posts: 1

Member Since:
October 23, 2019

Offline

October 23, 2019 - 12:27 am

All, I am able to achieve this in Excel but having difficulty with the coding in my power query. What I am want to do is flag the first instance of an ID number's "Priorty" status by the date is was first marked "Priority". Can anyone assist? Much appreciated!

ID Number	Status	Report Date	Flag First Instance
1117474752719690-00		10/10/2019
1117474752719690-00		10/11/2019
1117474752719690-00		10/14/2019
1117474752719690-00	Priority	10/15/2019	X
1117474752719690-00	Priority	10/17/2019
1117474752719690-00	Priority	10/18/2019
1117474752719690-00	Priority	10/21/2019
1117474752719690-00	Priority	10/22/2019
1117483188738324-01	Priority	10/10/2019	X
1117483188738324-01	Priority	10/11/2019
1117483188738324-01	Priority	10/14/2019
1117483188738324-01	Priority	10/15/2019
1117483188738324-01	Priority	10/17/2019
1117486867485829-11	Priority	10/10/2019	X
1117486867485829-11	Priority	10/11/2019
1117486867485829-11	Priority	10/14/2019
1117488038738324-01		10/10/2019
1117488038738324-01		10/11/2019
1117488038738324-01		10/14/2019
1117488038738324-01	Priority	10/15/2019	X
1117488038738324-01	Priority	10/17/2019
1117491697442259-09		10/10/2019
1117491697442259-09	Priority	10/11/2019	X
1117491697442259-09	Priority	10/14/2019
1117491697442259-09	Priority	10/15/2019
1117491697442259-09	Priority	10/17/2019
1117492187389862-06		10/10/2019
1117492187389862-06		10/11/2019
1117492187389862-06		10/14/2019
1117492187389862-06		10/15/2019
1117492187389862-06	Priority	10/17/2019	X
1117492187389862-06	Priority	10/18/2019
1117492187389862-06	Priority	10/21/2019
1117492187389862-06	Priority	10/22/2019

Mynda Treacy

Admin

Forum Posts: 4772

Member Since:
July 16, 2010

Offline

October 23, 2019 - 9:28 am

Hi LD,

Welcome to our forum!

You can use the 'Number Grouped Data Power Query' technique to find the first instance of each ID tagged with Priority. In the attached file I created a query to extract the ID Numbers with a Status of Priority, numbered them, filtered to keep only the first entries, then merged the queries to bring in the X flag. See file attached.

Mynda