Displaying data from SharePoint list into a simple bar chart

[Tom898]

So I am trying to set up a process. An audit form is linked to a sharepoint list that includes the columns: workspace, date, and then a column for each of the 5S' so Sort, Store, Shine, Sustian, Standardise. Under each of the 5S columns they have a number for each entry, e.g 4 / 6 / 7 / 2 / 9 and so I want to have a bar chart that can just display this one entry, filtered by date. As similar to the image as possible however that is done by converting the results into a percentage, this is fine as a solution. The second Image is how the data is displayed in the sharepoint list, essentially for each for i want to be able to display a bar chart, possibly using the filter tool on the dashboard as well to switch between the dates. I just can't get the bar chart to be refined to this.

[danextian]

Hi @Tom898 

Your data source needs to be transformed so each column name after Date becomes a category with another column corresponding its values. That would be something like

Sort 6

Sort 4

Sort 8

Store 6

Store 10

Store 6

In order to do that, go the query editor, select the first three columns, go to transform and select unpivot. You can rename the generated columns called attribute and value as desired. Those  7s'es should now be in one column with their corresponding values in the next column.

Below is just a sample of what unpivot does. Just ignore the data as it doesn't make sense to unpivot it

[Tom898]

When I put the unpivoted data in the chart, all the 7s catagories are shwoing as the same value, despite them all having different ones. Do you know why this may be?

[Tom898]

Okay I've figured that part out now, an question I have now is that my plan was to publish this dashboard (once finished) and then use power  automate so any new data put into the sharepoint list will automatically update into power BI, will this work with these columns unpivoted? 

[danextian]

Hi @Tom898 

You can use Power Automate to trigger a Power BI dataset refresh which means that the semantic model in the service gets refreshed with the updated data but as per documentation, the dataset refresh limit is 16 per day inclusive of API and scheduled refresh. If you wish to create a streaming/realtime dataset, you can't use Power Query so no unpivot. Here's how to create a streaming dataset with Power Automate + Power BI + SP List - https://www.youtube.com/watch?v=jmJJHFGwWFc&ab_channel=KeaPointTechTips 

But honestly, I find, the auto-update as the sharepoint list is updated an overkill. I'd be fine with getting the most updated report as of yesterday or as of the last few hours but not every second.

[Tom898]

I mean the sharepoint won't be getting that many updates, just when someone carrys out an audit. So am i unable to present the date in thr format I want while having it automatically update?

[danextian]

If you are within the API limit, you still can automatically trigger a semantic model refresh.  It's just that if you want more than that, you have to pay more.

[Tom898]

Okay. I still don't really understand your orignal solution, how do i display the data unpivoted. My main problem is that there isn't just a normal bar chart, they're all clustered or stacked

[Tom898]

How do I then access that back in power BI? Those columns now don't show up in the data tab. This is literally my first time trying to use power BI so don't know anything