Create a new table with last known record

[Rolf]

Hey,

I have a table with many records of equipment and where it has been. I would like to take this data to another table and keep only te most recent entry (row) of each equipment.

I have tried sorting on most recent but i then run into the issue that some equipment location hasnt been updated for a few months and would be way back in the list.

This is a small example of the data:

Date	Equipment	LAT	LON
1-1-2017 0:00	a	22,6147	120,281
1-1-2017 0:00	b	40,8438	29,2897
2-1-2017 0:00	a	1,25675	103,61
2-1-2017 0:00	a	51,3484	4,25857
2-1-2017 0:00	b	1,21571	103,642
3-1-2017 0:00	c	22,5245	120,302
3-1-2017 0:00	d	53,1894	5,42991
6-1-2017 0:00	d	51,4464	3,04585
6-1-2017 0:00	c	51,2334	2,92889

What i would like to see returned in a different table would look like this:

Date	Equipment	LAT	LON
2-1-2017 0:00	a	51,3484	4,25857
2-1-2017 0:00	b	1,21571	103,642
6-1-2017 0:00	c	51,2334	2,92889
6-1-2017 0:00	d	51,4464	3,04585

Any ideas?

[Zubair_Muhammad]

There should be a better way but this will do the job as well

Assuming your current table name is "Table1", create new table from ModellingTab

Table =
SUMMARIZE ( Table1, Table1[Equipment], "Max", MAX ( Table1[Date] ) )

Then create a relationship  between Table1 and above Table using "Equipment" field/column

Now your desired table can be created using DAX below

DesiredTable =
FILTER (
    Table1,
    Table1[Date] = RELATED ( 'Table'[Max] )
        && Table1[Equipment] = RELATED ( 'Table'[Equipment] )
)

[Rolf]

Thank you very much! The first part goes to plan. It gives me a list of unique equipment with their most recent time. However when i make the second table I do get a list with the other columns added, which is great, however there are duplicates between them all of a sudden. Have i overlooked something here?

Also a bit related: Is there a way to lookup a value based on two parameters (in the same row)? In this case equipment and date. In Excel I would just add an IF formula in the vlookup code.

I think this would solve the problem as well.  I would add a column in the filter table which would return a value based on the two columns.

[Zubair_Muhammad]

Hi @Rolf

Did you create a relationship between the orginal table and the summarized table created in first part?

[Rolf]

Yes, i have.

It is strange because there are no duplicate results in the original table. It seems to just create extra's. Also not all entries are duplicated, just a few.

[Zubair_Muhammad]

Rows 3 and 4 of the original table you posted have same date and same equipment i.e equipment a and date 2-1-2017 0:00

Thats why they would be duplicated

[Rolf]

Makes sense. Thanks a lot. Do you know of any way to do this in the Power Query environment as well?

[Zubair_Muhammad]

I think so it can be done using Merge Queries in Power Query.

Have got to leave. Will get back to you on this

[Zubair_Muhammad · Accepted solution]

Hi @Rolf

Sorry for getting back late. In Power Query, you can achieve exactly the same reults

1) Create a duplicate table/query
2) Use the "Group by" ....... Group by Equipment.......New Column with Maximum Date
3) Then Go to Merge Queries (merge the above table with orginal table)
4) Select Date and Equipment Columns in both tables (Two Column match)
5) Expand Table Selecting LAT and LON

[Rolf]

Was worth the wait 😉 This is exactly what i needed. Thank you very much. I had the same issue with duplicates but just added a remove duplicate step which gives me an excellent unique list of last known location.