cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericonline
Community Champion
Community Champion

Filter table by Distinct ID and latest timestamp

Hello,

Hoping someone can help me wrap my head around this. I need to filter the below table by Distinct equipmentID and latest timestamp.

 

The records highlighted below should be the results.

 

Sample data:

IDequipmentIDtimestampxvalueyvalue

1

1232021-03-03T060000ZTestTest2
21232021-03-03T050000ZTest0Test4
33452021-03-03T080000ZTestTestTEst
43452021-03-03T070000ZYesYep
54562021-03-03T080000ZSureUhuh

 

Something like:

ClearCollect(colLatestRecords,
    Filter(
        Distinct(TABLE_NAME, equipmentID),
        Max(timestamp)
    )
);

 But this is not correct. 

 

Any ideas?

Thank yoU!

7 REPLIES 7
Drrickryp
Super User II
Super User II

@ericonline 

Use AddColumns() for a formula that concatenate the equipment ID and the time stamp and use distinct on the new column.

ericonline
Community Champion
Community Champion

@Drrickryp , thanks for the reply. Where does the Distinct() come in? 

 

ClearCollect(colLatestRecords,
    Filter(
        AddColumns(TABLE_NAME,
            "uniqueLastRecord",
            Concatenate(ThisRecord.equipmentID, Text(ThisRecord.timestamp))
        ),
        ??
    )
)
RandyHayes
Super User III
Super User III

@ericonline 

I believe what you are looking for is this:

ForAll(
    GroupBy(
        AddColumns(
            TABLE_NAME,
            "cDate", DateTimeValue(timestamp)
        ),
        "equipmentID",
        "_recs"
    ),
    Patch(First(SortByColumns(_recs, "cDate", Descending)), {equipmentID: equipmentID})
) 

I hope this is helpful for you. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
ericonline
Community Champion
Community Champion

I will test this now. I was not expecting to see a Patch() here! 

RandyHayes
Super User III
Super User III

@ericonline 

Patch patches records and returns a record!  In this case, we are patching the equipmentid column back into the grouped record - because when you group by a column, that column is the column in the resulting table, not the grouped records.  So, we're just putting it back in to the record we want.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
ericonline
Community Champion
Community Champion

@RandyHayes , I understand the formula you proposed, but it looks like I'm hitting delegation.

Example:

SQL Table Rows: ~4500

Function: (as you proposed)

 

ClearCollect(colLatestRecords,
    ForAll(
        GroupBy(
            AddColumns(
                '[dbo].[TABLE_NAME]',
                "cDate", 
                timestamp
            ),
            "equipmentID",
            "_recs"
        ),
        Patch(
            First(SortByColumns(_recs, "cDate", Descending)), 
            {
                equipmentID: equipmentID
            }
        )
    )
)

 

Results: in a single record though there are many equipmentID's in the table (equipmentID is null though until ~ID 2500).

- "PowerApps encountered an error" message is because the field is blank (verified).

- I have AdvancedSettings/DataRowLimit at default 500

IDequipmentIDcdate
500"Powerapps encountered an error"2/21/2021 0500

 

Delegation issue:

So I tried a Filter function on the SQL table like so (timestamp is a DATETIME(2) data type):

 

Set(varLookback, Now() - 1);

ClearCollect(colLatestRecords,
    ForAll(
        GroupBy(
            AddColumns(
                Filter(
                    '[dbo].[TABLE_NAME]',
                    timestamp > varLookback
                ),
                "cDate", 
                timestamp
            ),
            "equipmentID",
            "_recs"
        ),
        Patch(
            First(SortByColumns(_recs, "cDate", Descending)), 
            {
                equipmentID: equipmentID
            }
        )
    )
)

 

But this always returns an empty collection. Shouldn't the dates in the timestamp column and varLookback be comparable in this case?

RandyHayes
Super User III
Super User III

@ericonline 

Yes...the formula I provided is not delegable.  The AddColumns is the issue there as it is not delegable.

However, delegation did not seem like and issue as you originally had Distinct - which is also not delegable.

 

You are on the right path though...to pre-filter the information going into the AddColumns.

But, your timestamps are going to be a problem to filter on as they are not going to be compatible with the date type you are putting in a variable.

 

My suggestion is that, since you are on SQL...can you just put together a View in SQL to return the filtered information i.e. last month worth of data?

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,216)