cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create a new table with last known record

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:

DateEquipmentLATLON
1-1-2017 0:00a22,6147120,281
1-1-2017 0:00b40,843829,2897
2-1-2017 0:00a1,25675103,61
2-1-2017 0:00a51,34844,25857
2-1-2017 0:00b1,21571103,642
3-1-2017 0:00c22,5245120,302
3-1-2017 0:00d53,18945,42991
6-1-2017 0:00d51,44643,04585
6-1-2017 0:00c51,23342,92889

 

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

DateEquipmentLATLON
2-1-2017 0:00a51,34844,25857
2-1-2017 0:00b1,21571103,642
6-1-2017 0:00c51,23342,92889
6-1-2017 0:00d51,44643,04585

 

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Create a new table with last known record

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


 

Try my new Power BI game Cross the River

View solution in original post

9 REPLIES 9
Highlighted
Super User III
Super User III

Re: Create a new table with last known record

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] )
)

 

Try my new Power BI game Cross the River
Highlighted
Frequent Visitor

Re: Create a new table with last known record

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.

Highlighted
Super User III
Super User III

Re: Create a new table with last known record

Hi @Rolf

 

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

Try my new Power BI game Cross the River
Highlighted
Frequent Visitor

Re: Create a new table with last known record

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.

Highlighted
Super User III
Super User III

Re: Create a new table with last known record

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

Try my new Power BI game Cross the River
Highlighted
Frequent Visitor

Re: Create a new table with last known record

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

Highlighted
Super User III
Super User III

Re: Create a new table with last known record

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

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

Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

Re: Create a new table with last known record

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


 

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Frequent Visitor

Re: Create a new table with last known record

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors