Skip to main content
cancel
Showing results for 
Search instead 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

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

 

I hope this helps you out!

 



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?

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

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
Fabcon_Europe_Social_Bogo

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.