Forum Discussion
Pick the latest record from source
I have data coming in from plant where sometimes they send updated information on a later time or updte the previous data if there was an error. How will I use Power Qury/PowerBI to pick the record with latest time.
Example
Record 1 time: 2020/08/24 17:20:00
Record 2 time: 2020/08/24 19:20:00
I want to pick the one that was sent at 19:20 and reject the record that was sent earlier
Attaching a sample for reference
Plant TransactionDate Produced Defcts rejected Audited
PE 2020/08/24 17:20:00 70 5 1 1
PE 2020/08/24 19:20:00 60 9 1 1
PTA 2020/08/21 16:25:00 20 1 0 0
PTA 2020/08/22 19:20:00 30 0 0 1
PTA 2020/09/07 10:00:00 120 5 5 0
PTA 2020/09/07 20:00:00 90 0 0 1
Thanks in advance.
Anonymous
In this case, no need to create a new table, you can add a new column to your table with the following code:You can download the file: HERE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
7 Replies
- Fowmy
Super User
Anonymous
Go to ModlingTab > New Table and paste the code, it will give you a new table with the latest records:Table = FILTER( Table, VAR _MaxDate = CALCULATE( MAX(Table[TransactionDate]),ALLEXCEPT(Table,Table[Plant])) RETURN Table[TransactionDate] = _MaxDate )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Thank You Fowmy
I am a newbie and if you can give me the sample PBIX, that will be kind of you. Still trying to apply what you asked
Plant TransactionDate Produced Defcts rejected Audited PE 2020/08/24 17:20:00 70 5 1 1 PE 2020/08/24 19:20:00 60 9 1 1 PTA 2020/08/21 16:25:00 20 1 0 0 PTA 2020/08/22 19:20:00 30 0 0 1 PTA 2020/09/07 10:00:00 120 5 5 0 PTA 2020/09/07 20:00:00 90 0 0 1