Forum Discussion

New Member

6 years ago

Solved

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

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 DDMM...

rajulshah
6 years ago
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.

Icon for Super User rank

Super User

6 years ago

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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601