CODEWITHSUNDEEP
powerbidaxpowerquery
Rowling
Rowling

Reputation: 213

How to get latest record for matching ID and date in Power BI Query Editor

I have two tables:

Table A, which indicates where my truck is for each day,

Date        Truck   Region
5/20/2018   1014    NY
5/21/2018   1014    NJ

and Table B (which contains when my truck inspection was done for each day). Sometimes there may be more than one inspection record, but I only need the last one by truck by day. As you can see, I added a rank column. Truck 1014 has two records for 5/20/2018, but the last one gets ranked as 1 (I will filter the table by 1).

Date        Time              Truck  Rank
5/20/2018   5/20/18 9:00 AM   1014    2
5/20/2018   5/20/18 2:00 PM   1014    1
5/21/2018   5/21/18 2:50 PM   1014    1

I want to merge those two tables together. The reason I ask how to do it in the query editor is that, in the relationship view, you cannot create a relationship on two columns. For example, in my example, I want to join data by date and by truck number, which I cannot. What is the right direction for this situation?

Upvotes: 1

Views: 8535

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40304

In the query editor, you can use the Merge Queries button under the Home tab.

(You'll need to hold down the Ctrl key to select multiple columns.)

Merge

Once you've merged, just expand the columns from Table B that you want to bring over (e.g. Time and Rank). If you did not filter Rank = 1 before merging, you can include it when expanding and filter afterward.

Note that you can also use the LOOKUPVALUE DAX function outside of the query editor. As a new column on Table A:

Time = LOOKUPVALUE('Table B'[Time],
           'Table B'[Date], [Date],
           'Table B'[Truck], [Truck],
           'Table B'[Rank], 1)

Upvotes: 1

Related Questions