Good day,
I have a Sharepoint list with 3 columns. Name,Status & Date. Each time an assets status is marked as "On" or "Off" an entry is made with Name = Asset name, Status = On or Off and Date = Todays date.
What I would like to do in my power app is have a table that shows how often an asset is off. Please see picture for an example.
Many thanks
Hi @Anonymous ,
You can refer to the following steps to realize your requirement:
1. Add a button and set the OnSelect property of button to:
Share
Report
All responses (
Answers (
ClearCollect(DateListCollection,DateList)//DateList is my sharepoint list like yours, this formula aims to add a collection which use the sharepoint list value.
|
2. Set the OnSelect property of button to:
ClearCollect(DateListCollect,GroupBy(DateListCollection,"Title","Status","Date","Others"))// this formula aims to coalesce column which is hidden column in sharepoint list:
3. Set the OnSelect property of button to:
ClearCollect(DateListCollectFinal,DropColumns(DateListCollect,"Others"))// this formula aims to drop the other colum:
|
4. Set the OnSelect property of button to:
ClearCollect(collect1,Ungroup(ForAll(Distinct(DateListCollectFinal,Title),Filter(DateListCollectFinal,Title=Result,Status="On")),"Value"))// this formula aims to get the records which the ‘Status’ is On
|
5. Set the OnSelect property of button to:
ClearCollect(collect2,Ungroup(ForAll(Distinct(DateListCollectFinal,Title),Filter(DateListCollectFinal,Title=Result,Status="Off")),"Value"))// this formula aims to get the records which the ‘Status’ is Off
6. Rename the column name of Title in collect1 and coolect2:
ClearCollect(collect1plus,RenameColumns(collect1,"Title","TheName1"))
ClearCollect(collect2plus,RenameColumns(collect2,"Title","TheName2"))7. Merge collect1 and collect2 based on the same value in “TheName1" and “TheName2":
ClearCollect(FinalCollect,AddColumns(collect1plus,"Off From",LookUp(collect2plus,TheName1=TheName2,Date)))//this formula aims to add a column named “Off From”
|
8. Drop the column ‘Status’:
ClearCollect(FianlCollection,DropColumns(FinalCollect,"Status"))9. Rename the “Date” column to”Back On”:
ClearCollect(FinalCollections,RenameColumns(FianlCollection,"Date","Back On"))10. Add a “Total days” column:
ClearCollect(FinalColl,AddColumns(FinalCollections,"Total Days",'Back On'-'Off From'))11. You can add a table control and set the FianlColl as data source and choose the filed as below:
|
Hope it helps
Thanks,
Arrow
405
