Getting a previous value using 2 matching columns

[SClarke501]

I have a set of data similar to this with an two dates, a start and finish, and a location that corresponds to the entry date.

ID	Entry Date	Exit Date	Entered	Left
1	12/12/2017	24/12/2017	USA
1	24/12/2017	03/02/2018	Canada
1	03/02/2018		Mexico
2	04/03/2020	06/04/2020	UK
2	06/04/2020		Ireland
3	12/09/2020	13/09/2020	Australia
3	13/09/2020	24/11/2020	China
3	24/11/2020	10/01/2021	Taiwan
3	10/01/2021		Japan

I'm trying to use Power Query to create a new column "Left" that shows where that ID entered from. The idea is to check the "Entry Date" for each row and match it against the "Exit Date" column where the ID also matches, and return the location that ID was previously in.

For example:

ID	Entry Date	Exit Date	Entered	Left
1	12/12/2017	24/12/2017	USA
1	24/12/2017	03/02/2018	Canada	USA
1	03/02/2018		Mexico	Canada
2	04/03/2020	06/04/2020	UK
2	06/04/2020		Ireland	UK
3	12/09/2020	13/09/2020	Australia
3	13/09/2020	24/11/2020	China	Australia
3	24/11/2020	10/01/2021	Taiwan	China
3	10/01/2021		Japan	Taiwan

I can already achieve this using an excel formula: =XLOOKUP(A2&B2,A:A&C:C, D:D, "")

However my real dataset is massive and applying the formula to every row crashes Excel, so I've turned to DAX/Power Query to try and recreate it using LOOKUPVALUE, but I'm having trouble figuring it out.

[Anonymous · Accepted solution]

Hi @SClarke501 ,

You can create a calculated column as below, please find the details in the attachment.

Left = 
VAR _predate = 
    CALCULATE (
        MAX ( 'Table'[Entry Date] ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Entry Date] < EARLIER ( 'Table'[Entry Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Entered] ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Entry Date] = _predate
        )
    )

Best Regards

[Anonymous]

Hey @SClarke501 ,

I have created two extra columns to use the LOOKUPVALUE function:

Enrty-date = 'Table'[Entry] & "-" & 'Table'[ID]

Exit-Date = 'Table'[Exit] & "-" & 'Table'[ID]

Based on these keys, we can use the LOOKUPVALUE function:

Left = LOOKUPVALUE('Table'[Entered],'Table'[Exit-Date],'Table'[Enrty-date])

I hope this helps you out!

[SClarke501]

Hi @Anonymous 

Great idea on the extra columns, hadn't thought of that. 

However I'm getting an error on the LOOKUPVALUE: A table of multiple values was supplied where a single value was expected.

Is this because the "Entered" column has duplicates in the real data?

[Anonymous]

Hi @SClarke501,

No, the entered column should not be the problem. The lookup values has to be unique. That is why I created these keys. I assumed that these are unique. So if an ID enters or exits an country twice on the same day, the key is not unique any more. Can you search the Enrty-date and Exit-date columns we created for duplicates?

[SClarke501]

Yes unfortunately I spotted a duplicate. Very surprising since my real data has datetime instead of just date. But there are two entries that apparently happen on the exact same date at the exact same time!

Is there any way around this? I thought to maybe append some more data to the new columns to make them unique but there isn't anything to use.

[Anonymous]

Hey @SClarke501,

That is a bummer.. 

You would assume that datetime is unique enough. Of course, it is not possible to leave or enter two countries at the same time at exactly the same time.

If I was responsible for the report, I would report your observation back to the owner of the dataset. This must be an incorrect entry.

I can not come up with something to make it more unique.. 

[SClarke501]

The actual data is not about moving between countries, its about tasks being moved on a kanban board.

I suppose I should have come up with a better set of false data to represent my problem, apologies.

I think I may just have to do some cleansing and handle duplicate columns. I appreciate your help either way