cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SClarke501
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.

 

IDEntry DateExit DateEnteredLeft
112/12/201724/12/2017USA 
124/12/201703/02/2018Canada 
103/02/2018 Mexico 
204/03/202006/04/2020UK 
206/04/2020 Ireland 
312/09/202013/09/2020Australia 
313/09/202024/11/2020China 
324/11/202010/01/2021Taiwan 
310/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:

 

IDEntry DateExit DateEnteredLeft
112/12/201724/12/2017USA 
124/12/201703/02/2018CanadaUSA
103/02/2018 MexicoCanada
204/03/202006/04/2020UK 
206/04/2020 IrelandUK
312/09/202013/09/2020Australia 
313/09/202024/11/2020ChinaAustralia
324/11/202010/01/2021TaiwanChina
310/01/2021 JapanTaiwan

 

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
yingyinr
Community Support
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
        )
    )

yingyinr_0-1649936271859.pngBest 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.

View solution in original post

7 REPLIES 7
yingyinr
Community Support
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
        )
    )

yingyinr_0-1649936271859.pngBest 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.
PandaHans17
New Member

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])
PandaHans17_0-1649336165674.pngPandaHans17_1-1649336178637.png

 

I hope this helps you out!

 



Hi @PandaHans17 

 

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?

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?

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.

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

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.