Forum Discussion
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
Community ChampionThere 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.
- Zubair_Muhammad
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.
