Unanswered

Create table report based on SharePoint list

(0) Share

Report

Posted on by msago

Hi, I am a complete newbie at PowerApps, but I have a strong believe it could help me getting table reports to my colleagues on their mobile devices in real time.
I have a normal SP-list with several columns, but I only want to use a few in order to return a simple report table.

Here is an example with the structure and some fictional data, but the principle is the same:

Year (choice list)	Contact (choice list: Website; Call; Mail)
2020	Website
2020	Call
2019	Website
2019	Website
2019	Mail
2018	Call

Now I would like it to return a table, combining the 'year' column and counting the 'contact' column. Plus adding a yearly 'total' column.
For example:

Year	Website	Call	Mail	Total
2020	1	1	0	2
2019	2	0	1	3
2018	0	1	0	1

It may seem very simple, but having no experience at all, believe me this is quite a challenge. I have been looking around on the internet, and saw a lot of pieces here and there, but was unable to find the exact example.

And a small sub question: What if the value isn't in a choice column of my SP-list, but in a lookup table?

Could anyone point me in the right direction and thereby enforce my initial believe that this could also be used for simple requirements? Highly appreciated ! 🙏

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @msago ,

According to your description, I assume:
You have a lookup column, called "LookupTest".
The column related to the lookup column is the "Test" column, and its data type is Single line of text.
You display the result on the data table control.

I've made a test for your reference:
1\ This is my “LIST36” list.

2\ This is my “LIST37” list.

3\ Add a data table control and set its Items property to:
AddColumns( AddColumns( RenameColumns( Choices(LIST36.Year), "Value", "Year1" ), "Website", CountRows( Filter( LIST36, LIST36[@Year].Value = Year1, Contact.Value = "Website" ) ), "Call", CountRows( Filter( LIST36, LIST36[@Year].Value = Year1, Contact.Value = "Call" ) ), "Mail", CountRows( Filter( LIST36, LIST36[@Year].Value = Year1, Contact.Value = "Mail" ) ), "111", CountRows( Filter( LIST36, LIST36[@Year].Value = Year1, LookupTest.Value = "111" ) ), "222", CountRows( Filter( LIST36, LIST36[@Year].Value = Year1, LookupTest.Value = "222" ) ) ), "Total", '111' + '222' + Website + Call + Mail )

4\ The result is as follows:

Best Regards,
Wearsky

Was this reply helpful? Yes No
msago 2 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xiaochen-msft
Thank you very much for your help !
I've changed the Items property related to my real SPlist (see beneath), but somehow it doesn't really give the expected result. Note: apparently I need to use ; instead of , as well. Maybe it's regionally bound?
Could you please have a look at it and advice where it could have gone wrong?
Thanks a lot ! 😉

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @msago ,
Could you tell me:
Did your formula report an error?

If the formula reports an error, please tell me the content of the error.
In addition, you could try to refresh the data source, or close the app and reopen it.

Best Regards,
Wearsky

Was this reply helpful? Yes No