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

Create table report based on SharePoint list

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)
2020Website
2020Call
2019Website
2019Website
2019Mail
2018Call

 

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:

YearWebsiteCallMailTotal
20201102
20192013
20180101

 

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 ! 🙏
 

3 REPLIES 3
v-xiaochen-msft
Community Support
Community Support

Hi @msago ,

 

According to your description, I assume:

  1. You have a lookup column, called "LookupTest".
  2. The column related to the lookup column is the "Test" column, and its data type is Single line of text.
  3. You display the result on the data table control.

 

I've made a test for your reference:

1\ This is my LIST36 list.

v-xiaochen-msft_0-1608108122340.jpeg

 

 

2\ This is my LIST37 list.

v-xiaochen-msft_1-1608108122341.png

 

 

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:

v-xiaochen-msft_2-1608108122342.png

 

 

Best Regards,

Wearsky

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? 

msago_1-1608130152526.png

Could you please have a look at it and advice where it could have gone wrong? 
Thanks a lot ! 😉

 

 

v-xiaochen-msft
Community Support
Community Support

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,817)