Pick the latest record from source

[Anonymous]

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.

[Fowmy]

@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 🙂

YouTube  LinkedIn

[Anonymous]

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

[Fowmy]

@Anonymous 

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 🙂

YouTube  LinkedIn

[Anonymous]

Thank You Very Much for looking into this. Regret, I did not explain my requirements properly. Sorry about that

If on a particular day, there are 2 entries, I need to reject the old record and pick the latest record, all other records for previous dates will remain as is. To further clarify, the table below will help.

I will then use the records that says KEEP and pivot them.

Plant	TransactionDate	Produced	Defcts	rejected	Audited	KEEP OR REJECT
PE	2020-08-24 17:20:00	70	5	1	1	REJECT
PE	2020-08-24 19:20:00	60	9	1	1	KEEP
PTA	2020-08-21 16:25:00	20	1	0	0	KEEP
PTA	2020-08-22 19:20:00	30	0	0	1	KEEP
PTA	2020-09-07 10:00:00	120	5	5	0	REJECT
PTA	2020-09-07 20:00:00	90	0	0	1	KEEP
PE	2020-09-07 10:00:00	50	0	0	0	KEEP
PE	2020-08-22 19:20:00	20	0	0	0	KEEP

Regards.

[Fowmy · Accepted solution]

@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 🙂

YouTube  LinkedIn

[Anonymous]

Thanks a ton. Much appreciate your time and help. I was actually thinking that it can be done in PowerQuery but it is done in PowerBI.

Is there a formal training for PowerBI formulas that you just did that one can learn about from YouTube or some other forums?

[Fowmy]

@Anonymous 

I can direct you to a book by @Greg_Deckler  on multiple regular DAX patters: https://www.amazon.com/gp/product/B085RM7C9J

And, you should not miss this: https://www.sqlbi.com/

Good luck!

________________________

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 🙂

YouTube  LinkedIn