cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Getting a previous value using 2 matching columns

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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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!

Frequent Visitor

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
Not applicable

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?

Frequent Visitor

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
Not applicable

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

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.