Creating a pivot table within the app

[Wichowich]

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!

[v-monli-msft]

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

[Wichowich]

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]

Hi @Wichowich have you seen the follwoing blog post? 

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

It may help you acheive your needs. 

@Anonymous 

[gdavenport]

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!

[Anonymous]

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

[Stemmons_PowerU]

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]

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

[Anonymous]

Sadly, no

[danderson2692 · Accepted solution]

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:

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