Lookup Value from related Row - Related with a key

[ashleylinkewich]

Hi all,

I want to look up a related value from a previous row in a table. It looks like this: 

Contract	Job	Returned	Load	Key
A	B	0	789	12345
A	B	1	****	12345
A	B	0	456	67891
A	B	0	****	67891
A	B	1	****	67891

Basically I want to fill in the load column where the returned column is equal to 1, using the key column, i.e the first **** would be 789 and the second/third 456.

Is there any way to do this? I couldn't get my syntax right on either earlier or lookup functions. 

Thank you! 

[v-lili6-msft · Accepted solution]

hi @ashleylinkewich 

Basically I want to fill in the load column where the returned column is equal to 1, using the key column, i.e the first **** would be 789 and the second/third 456.

the returned column of the second **** is 0, it doesn't meet the conditional.

Then you could try this way:

First, you need  to add an index column in the table.

Second, create a column as below:

Column = 
IF (
    'Table'[Load] <> "****",
    'Table'[Load],
    IF (
        'Table'[Returned] = 1,
        CALCULATE (
            MAX ( 'Table'[Load] ),
            FILTER (
                'Table',
                'Table'[Key] = EARLIER ( 'Table'[Key] )
                    && 'Table'[Index] < EARLIER ( 'Table'[Index] )
            )
        ),
        "****"
    )
)

or

Column 2 = 
IF (
    'Table'[Load] <> "****",
    'Table'[Load],
    IF (
        'Table'[Returned] = 1,
        CALCULATE (
            MAX ( 'Table'[Load] ),
            FILTER (
                'Table',
                'Table'[Key] = EARLIER ( 'Table'[Key] )
                    && 'Table'[Index] < EARLIER ( 'Table'[Index] )
            )
        )
))

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[HotChilli]

Could you clarify the logic please?

"fill in the load column where the returned column is equal to 1"  this would leave 2 records to be filled,

but "i.e the first **** would be 789 and the second/third 456." that would be 3 records.

If all records are to be filled , this would be a Fill Down in Power Query (make sure the table is sorted correctly first)