cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iospriggs
Frequent Visitor

PowerApp Table

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

Screenshot 2021-01-21 at 11.58.19.png

1 ACCEPTED SOLUTION

Accepted Solutions
v-yangar-msft
Microsoft
Microsoft

Hi @iospriggs ,

 

You can refer to the following steps to realize your requirement:

1. Add a button and set the OnSelect property of button to:

ClearCollect(DateListCollection,DateList)

   //DateList is my sharepoint list like yours, this formula aims to add a collection which use the sharepoint list value.

v-yangar-msft_0-1611309297506.png

 

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:

v-yangar-msft_1-1611309297509.png

 

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

v-yangar-msft_2-1611309297512.png

 

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

v-yangar-msft_3-1611309297513.png

 

8. Drop the column Status:

ClearCollect(FianlCollection,DropColumns(FinalCollect,"Status"))

9. Rename the Date column toBack 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:

v-yangar-msft_4-1611309297515.png

 

 

Hope it helps

 

Thanks,

Arrow

View solution in original post

1 REPLY 1
v-yangar-msft
Microsoft
Microsoft

Hi @iospriggs ,

 

You can refer to the following steps to realize your requirement:

1. Add a button and set the OnSelect property of button to:

ClearCollect(DateListCollection,DateList)

   //DateList is my sharepoint list like yours, this formula aims to add a collection which use the sharepoint list value.

v-yangar-msft_0-1611309297506.png

 

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:

v-yangar-msft_1-1611309297509.png

 

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

v-yangar-msft_2-1611309297512.png

 

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

v-yangar-msft_3-1611309297513.png

 

8. Drop the column Status:

ClearCollect(FianlCollection,DropColumns(FinalCollect,"Status"))

9. Rename the Date column toBack 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:

v-yangar-msft_4-1611309297515.png

 

 

Hope it helps

 

Thanks,

Arrow

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (71,999)