Look up value in different row and return row in Query editor

[DavidBlockAU]

I have done some searching and found a few posts close to what I'm after, but can't quite get the result I need! In particular, this was real close. 

I have a table like this (date format is DDMMYYYY):

JobNo	StageID	TargetDate	ActualDate
12	100	01/01/2020	02/01/2020
12	200	07/01/2020	06/01/2020
12	300	14/01/2020	10/01/2020
12	400	31/01/2020	21/01/2020
28	100	03/01/2020	03/01/2020
28	200	05/01/2020	07/01/2020
28	400	15/01/2020	21/01/2020
28	500	20/01/2020	30/01/2020

I want to return the "Scheduled Start Date" for a particular job (being the target date where StageID = 100 for a job) in a separate column in query editor so my table will look something like this:

JobNo	StageID	TargetDate	ActualDate	StartDate
12	100	01/01/2020	02/01/2020	01/01/2020
12	200	07/01/2020	06/01/2020	01/01/2020
12	300	14/01/2020	10/01/2020	01/01/2020
12	400	31/01/2020	21/01/2020	01/01/2020
28	100	03/01/2020	03/01/2020	03/01/2020
28	200	05/01/2020	07/01/2020	03/01/2020
28	400	15/01/2020	21/01/2020	03/01/2020
28	500	20/01/2020	30/01/2020	03/01/2020

Any help would be much appreciated!

[amitchandak]

Make sure it is a date column, detected by power BI. I created the following two

Target Date New = mid(schedulestartdate[TargetDate],4,2) &"/"& left(schedulestartdate[TargetDate],2) & "/" & right(schedulestartdate[TargetDate],4)

start Date= LOOKUPVALUE(schedulestartdate[Target Date New],schedulestartdate[JobNo],FIRSTNONBLANK(schedulestartdate[JobNo],TRUE()),schedulestartdate[StageID],100,FIRSTNONBLANK(schedulestartdate[Target Date New],TRUE()))

Refer : https://www.dropbox.com/s/bours0j29rwyy3o/schedulestartdate.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[rajulshah · Accepted solution]

Hello @DavidBlockAU,

You can try the following steps:

1. Create a duplicate of the table. Go to that duplicate table.

2. Go to Transform > Group By. Enter JobNo as Grouping, New column name - 'StartDate', Operation - 'Min' & Column - 'TargetDate'. Click on OK.
3. Go to the original table > Merge Queries. Merge original and duplicate table with JobNo column.

4. Expand the column and select only 'StartDate. You will have your column.

Hope this helps.

[DavidBlockAU]

Can't believe I didn't think to do this, already have 3 merges in the model! Thanks for the suggestion @rajulshah!