Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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
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] ) )
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.
Hi @Rolf
Did you create a relationship between the orginal table and the summarized table created in first part?
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.
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
Makes sense. Thanks a lot. Do you know of any way to do this in the Power Query environment as well?
I think so it can be done using Merge Queries in Power Query.
Have got to leave. Will get back to you on this
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
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.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
103 | |
77 | |
71 |