Sorting subset of a table

[auggie9]

Hello all,

I am a newbie. Sorry if this is a repeated question and was already answered. I am trying to sort a subset of a table and use the sorted resultset for further calculations. I am bringing sensors data as shown below in to a table(sensors) in Powerapps.

Sensor	Valid	Start_Date	End_Date	Other Data
S1	Yes	9/8/2020	9/9/2020
S2	No	8/21/2020	8/23/2020
S3	Yes	8/25/2020	8/27/2020
S4	No	8/30/2020	9/2/2020
S5	Yes	9/2/2020	9/3/2020
S6	No	9/5/2020	9/7/2020
S7	No	9/7/2020	9/8/2020
S8	No	9/10/2020	9/11/2020
S9	No	9/9/2020	9/10/2020

I am planning to do the following with the App OnStart.

1. Filter this table to records that are valid='Yes'. Result below

Sensor	Valid	Start_Date	End_Date	Other Data
S1	Yes	9/8/2020	9/9/2020
S3	Yes	8/25/2020	8/27/2020
S5	Yes	9/2/2020	9/3/2020

2. The Start_Date and End_date columns are text. Convert them to DateValue. Not sure how to achieve this on all items in a column.
3. Sort the records based on Start_Date to show the earlier date first. Expected resultset below.

Sensor	Valid	Start_Date	End_Date	Other Data
S3	Yes	8/25/2020	8/27/2020
S5	Yes	9/2/2020	9/3/2020
S1	Yes	9/8/2020	9/9/2020

4. Save the sorted records to a collection for later use.
5. Use the sensor list after sorting, in a dropdown menu. In the same order as in the sorted result above(S3, S5, S1)
6. Find earliest date from the Start_Date column and assign it to a variable Range_Start_Date. Here Range_Start_Date = 8/25/2020
7. Find latest date from the End_Date column and assign it to a variable Range_End_Date(Here 9/9/2020)
8. Filter another table (measurements) from Range_Start_Date to Range_End_Date.

Thanks in advance for idea to solve this.

[v-qiaqi-msft · Accepted solution]

Hi@auggie9,

Could you please tell me that which data source do you use, Excel or SharePoint?

I assume that you use the Excel data storing in the OneDrive.

I have a test on my side, please take a try as below.

Add a DataTable.

1) Filter this table to records that are valid='Yes'.

Set the Items property of DataTable:

Filter(sensors,Valid="Yes")

2) The Start_Date and End_date columns are text. Convert them to DateValue. 

Set the Text property of the corresponding column as:

DateValue(ThisItem.Start_Date,"en-US")

DateValue(ThisItem.End_Date,"en-US")

Note: Not sure if you want to convert all the date into Date format into a collection. If you want to convert them into a collection directly as a Date format, please try as below:

ForAll(sensors,Collect(ColSen,{Sensor:Sensor,Valid:Valid,Start_Date:DateValue(Start_Date,"en-US"),End_Date:DateValue(End_Date,"en-US")}))

3) Sort the records based on Start_Date to show the earlier date first. 

Set the Items property of DataTable:

Sort(Filter(ColSen,Valid="Yes"),Start_Date,Ascending)

4) Save the sorted records to a collection for later use.

Set the OnStart property of the App as below:

Collect(ColSensors,Sort(Filter(ColSen,Valid="Yes"),Start_Date,Ascending))

5) Use the sensor list after sorting, in a dropdown menu. In the same order as in the sorted result above(S3, S5, S1)

Add a Dropdown and set its Items property as below:

ColSensors.Sensor

6) Find earliest date from the Start_Date column and assign it to a variable Range_Start_Date.

Set the OnVisible of the current screen as below:

Set(Range_Start_Date,First(ColSensors).Start_Date)

7) Find latest date from the End_Date column and assign it to a variable Range_End_Date(Here 9/9/2020)

Set(Range_End_Date,Last(ColSensors).End_Date)

😎 Filter another table (measurements) from Range_Start_Date to Range_End_Date.

Filter(measurements,(StartDate>= Range_Start_Date &&StartDate<=Range_End_Date) Or (EndDate>= Range_Start_Date &&EndDate <=Range_End_Date))

I assume that your measurements has similar start date and end date columns, please check as above.

Regards,

Qi

[auggie9]

Thank you so much. Every thing worked for me. Yes you are right, I am using excel in Onedrive as data source. Appreciate your help very much.