Creating a table with Only the latest rows

[lcasey]

Does anyone Know how to create a table from JUST the latest rows?

For example, 

In the picture below

# 1  There are 8 records with CaseIDid 12

#2   Out of those 12 records, only the very latest row with the Max Event_date 12/03/2018  should be returned

How can Power BI simply create a new table and filter out every single row Except for the very latest row with the highest Event_date?

[lcasey]

Hello,

Does anyone know how to create a table from another table by simply using the latest  record in the original table?

[samdthompson]

Hello, you could do this in two ways:

1. in power query filter the date to the max date using groupby and max on the date column

2. using DAX make a new table (modelling ribbon): NEW TABLE = CALCULATETABLE('Date', LASTDATE('Date'[Calendar_Date]))

// If this is a solution please mark as such

[lcasey]

The new table needs to iterate through every single row , which there are 800 rows , then out of those 800 rows , only return the rows with the latest event date for each case.

I have posted several times on this and it seems Power BI cant do it.   Maybee that why people are using SQL for any type of real reporting as something this simple just seems to not be possible

Let me know if you can think of anything.

Thanks

[samdthompson]

So is it a case of wanting to return not the row where the date is the maximum of all rows but the rows where the date is the maximum for that particular caseid? For eample you might have caseid1 with a maximum date of 1/1/2018 and caseid with a maximum date of 31/10/2018 and you wish to show both?

If thats the case do it in power query. group by caseid and return the max date in the aggregation. something like 5 or 6 clicks to do it

// if this is a solution please mark as such

[lcasey]

Hmmm... Maybee I am not explaining it too well as grouping does not work at all for what I am trying to do.

#1 - I have 800 rows with a CASEID  and and EVENT_DATE and NEXT_EVENT_DATE

#2 - I need to go through each row and only return the Latest EVENT_DATE per Case.

Note - This means that there will be no duplicate case numbers, and that for the case number that gets returned Only the row with the Latest EVENT_DATE is returned. Some cases have 5 or more rows of events with differentr dates.

#3 - That means only 1 case and only 1 event per case is returned. A single row for each case will be returned.

#4 - The resulting dataset should be around 300 rows becouse there would be NO duplicates what so ever and ONLY the row with the Latest Event Date would be returned for each case.

I have tried for several hours and nothing works.

[samdthompson]

Yeah that sound like a simple task using group by in powerquery. Hang on will jimmy up a picture of how to do it

sorry about the excel based picture. i use it for stuff like that.

// If this is a solution please mark as such

[lcasey]

Thanks!   I am hoping you are right, becouse whenever I group by a field in Power Query , I loose every other field.

[samdthompson]

oh the loosing all the other columns? yeah. in the first row of the groupby it has a count rows thing. Change that to all rows. then put in a second aggregation. when you get the table, it will have a column headed as table. click that and expand out all the ones you want. you will then have back all the rows but to filter it down you could do a custom column with event date = the max date and filter out the ones where is doesnt match. I made a picture for that one too:

// If this is a solution please mark as such

[lcasey]

Hi,

I have tried every possible combination and there is no way to make this work.

If I only group by Case ID and then aggregate by Max Event_Date I get the Correct amount of records, but as soon as I try to add in any other field it does not work.

[samdthompson]

when you say addin other firelds do you mean the expand? if so do the wee date = max date calc then filter like i put in the image before. That will get rid of the non max date rows.

 

// if this is a solution please mark as such

[lcasey]

Hmmmm--- 

The pictures are too small for me to see so I may be missing something, but I still only see 1 column.    

Is it possible to share the pictures ?

[lcasey]

This is what I see,

There are hundreds of rows with the word Table.  If I click the word Table I immediatly get duplicate values.

[lcasey]

That did not work,

It only returned 1 record out of hundreds.