cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
auggie9
New Member

Sorting subset of a table

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.

 

SensorValidStart_DateEnd_DateOther Data
S1Yes9/8/20209/9/2020 
S2No8/21/20208/23/2020 
S3Yes8/25/20208/27/2020 
S4No8/30/20209/2/2020 
S5Yes9/2/20209/3/2020 
S6No9/5/20209/7/2020 
S7No9/7/20209/8/2020 
S8No9/10/20209/11/2020 
S9No9/9/20209/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

 

SensorValidStart_DateEnd_DateOther Data
S1Yes9/8/20209/9/2020 
S3Yes8/25/20208/27/2020 
S5Yes9/2/20209/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.

SensorValidStart_DateEnd_DateOther Data
S3Yes8/25/20208/27/2020 
S5Yes9/2/20209/3/2020 
S1Yes9/8/20209/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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

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

Best Regards,
Qi

View solution in original post

2 REPLIES 2
v-qiaqi-msft
Community Support
Community Support

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

Best Regards,
Qi
auggie9
New Member

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. 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,523)