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

Creating a pivot table within the app

Hi,

 

I am trying to create an app from a Sharepoint custom list. I want to create a simple column chart that says:

We have n1 number of records of category y1, n2 number of records of category y2 and n3 number of records of category y3.

 

Basically summarizing a long table in a "pivot table", so then I can create a column chart.

 

How can I do this?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
danderson2692
Frequent Visitor

I was able to make this work by following the approach listed in this thread: https://powerusers.microsoft.com/t5/Building-Power-Apps/Access-Parent-s-Gallery-Item-in-a-Nested-Gal...

 

In my case since I used the Distinct() approach that gdavenport recommended, it was awkward to create an ID field so instead I used the headers of both nested galleries to derive a value using the labels. In other words:

  1. I had a vertical gallery which gave me discrete Category rows using Distinct(etcUnpivot,Category) where etcUnpivot was my table
  2. I had a nested horizontal gallery which gave me discrete Date columns using Distinct(etcUnpivot,Date)
  3. To show the values, I used a label with LookUp(etcUnpivot,Category=Label5.Text && Date=Label8.Text,Value) where Label5 was my row label in the vertical gallery, Label8 was my column label in the horizontal gallery, and "Value" is the value I wanted to present in the pivot table.

This was the result which is good enough for my situation. I'm not sure if it would be possible to prevent headers from being repeated on each row or to make it so a single horizontal scrollbar can scroll for all rows:

Capture.PNG

 

This worked for me using both a collection and a sharepoint list as a data source.

View solution in original post

11 REPLIES 11
v-monli-msft
Community Support
Community Support

Hi @Wichowich ,

 

You can show data in a line, pie, or bar chart in PowerApps, learn how to do this here:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/use-line-pie-bar-chart

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-monli-msft!

Thanks for the answer, unfortunately, this is not what I am looking for.

 

In Excel analogy, my data is raw, so I have many rows with the category name. Pie charts and bar charts cannot be applied to this data without consolidation or summary, which you accomplish with Pivots.

 

Thanks!

Anonymous
Not applicable

Hi @Wichowich have you seen the follwoing blog post? 

 

https://eriksvensen.wordpress.com/category/pivottable-2/ 

 

It may help you acheive your needs. 

 

@Anonymous 

Here’s an idea: Use Distinct() on the table you want to pivot, naming the field whose values you want to turn into column names. Then set up a horizontal gallery with the Distinct expressions Result table as the data source. Inside the gallery, nest a second gallery - this time a vertical one - with the items property set to a filter on the original data source on the value that matches the corresponding horizontal gallery’s value. I hope this makes sense!
hsy
Helper I
Helper I

Having the same issue. 

 

What I am trying to do is to embed a Power BI tile and display pivot table in Power BI.

 

The thing is Power BI is not refreshed in real time, user might struggling see how they have to wait for half hour for the data to be displayed

Hi, 

 

One way we've overcome refresh issues for our PowerBi tiles within PowerApps is adding a button that utilizes a powerautomate flow to refresh data. 

https://youtu.be/7vCD8Zu686Q << is a video on how to setup a button using a Post Request > API within PowerBi, but the same function can be added to a button in PowerApps.  

Hope this helps, 

 

Andrew

semfred
New Member

Hi did you resolve this? Thank you please share your solution.

Sadly, no

danderson2692
Frequent Visitor

I was able to make this work by following the approach listed in this thread: https://powerusers.microsoft.com/t5/Building-Power-Apps/Access-Parent-s-Gallery-Item-in-a-Nested-Gal...

 

In my case since I used the Distinct() approach that gdavenport recommended, it was awkward to create an ID field so instead I used the headers of both nested galleries to derive a value using the labels. In other words:

  1. I had a vertical gallery which gave me discrete Category rows using Distinct(etcUnpivot,Category) where etcUnpivot was my table
  2. I had a nested horizontal gallery which gave me discrete Date columns using Distinct(etcUnpivot,Date)
  3. To show the values, I used a label with LookUp(etcUnpivot,Category=Label5.Text && Date=Label8.Text,Value) where Label5 was my row label in the vertical gallery, Label8 was my column label in the horizontal gallery, and "Value" is the value I wanted to present in the pivot table.

This was the result which is good enough for my situation. I'm not sure if it would be possible to prevent headers from being repeated on each row or to make it so a single horizontal scrollbar can scroll for all rows:

Capture.PNG

 

This worked for me using both a collection and a sharepoint list as a data source.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,913)