Forum Discussion
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 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!
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.
3 Replies
rajulshahResident Rockstar
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.
Can't believe I didn't think to do this, already have 3 merges in the model! Thanks for the suggestion rajulshah!
- amitchandak
Super User
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
