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.
Solved! Go to 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
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
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.
User | Count |
---|---|
262 | |
110 | |
92 | |
55 | |
41 |