Power BI Realtime Reporting on SharePoint List Data

This video will walk through the process of how to do Power BI real-time reporting using SharePoint Lists data. Quite often we get requirements from businesses to create a real-time Power BI report based on SharePoint list data. but, when we connect to SharePoint list data from Power BI, we don’t have the option of the direct query. Instead, the only available option is to import query mode. This method only allows the data refreshes up to 8 times per day with a Power BI Pro license and up to 48times with Power BI Premium.
In this demo, I will create a streaming dataset and will add new content using the Power Automate flow.
★ TABLE OF CONTENTS ★
• 00:00 Intro
• 02:07 Create Streaming Dataset using Power BI Portal
• 04:18 Create Sample Report in Power BI using Streaming Dataset
• 06:16 Create Power AUtomate Flow to Update Power BI Dataset
• 10:48 Outro & Subscribe
🔴 ** BE OUR FRIEND **
✅ Website: www.keapoint.com
✅ LinkedIn: h / 18782324
✅ Twitter: / kea_point
✅ Facebook: / keapointuk
🔎 HASHTAGS 🔎
#PowerBI
#PowerAutomate
#SharePoint

[@anastasianikolovski8337]

Great tutorial video. Was able to finally pull my data from the SP to a PBI report directly - which is near-real time :) Just amazing. One aspect didn't work in my case. It didn't pull the initial data from the SP. Although it is fine in my case, as the data I have is for only for training purposes, this might present an issue for anyone out there with the existing data records they want to preserve and pull to the streaming dataset. Anyway, thank you very much.

[@KeaPointTechTips]

Glad it helped Anastasia, You may want to explore direct query with Dataverse instead of SharePoint list

[@baci1423]

Or just create an "import query mode" report and create a flow with "When an item is created or modified" trigger and "Refresh dataset" action. Includes every type of changes and it is working like a charm.

[@MrFxJ]

hits a daily limit of refresh so it basically prevents you from creating real-time dashboard

[@akshay0249]

But limit of 8 updates still applies. The flow will fail if limit of 8 is exhausted for Pro users.

[@gipbwok2008]

It worked like a charm for me. Thanks KeaPoint.

[@KeaPointTechTips]

Great to hear!

[@FRANKWHITE1996]

Thanks for sharing! 👍

[@KeaPointTechTips]

No problem 👍

[@simonbuchner1477]

Thanks a lot! You really helped me out😀

[@KeaPointTechTips]

You are welcome!

[@komanglayang7861]

Hello, thank you very much for the helpful video. I was wondering if you have covered another topic similar to this one but with row update and deletion using Power Automate?

[@KeaPointTechTips]

Change item limit in the flow get items action step.

[@adriansilwimba9764]

Great Tutorial. I am an intern working for a Renewable Energy Company and I have been tasked to create a PnL Dashboard that can automatically be updated whenever the files are updated with new rows. We do use share point at my organization but I am not quite sure on how I create relational Database to automatically update in power BI.

[@KeaPointTechTips]

If you share more details, I would be happy to assist.

[@arunv1909]

do u faced the problem of date not matching if u use sharepoint as datasource in power bi ?

[@sanarajpoot7542]

Great tutorial video

[@KeaPointTechTips]

Thanks.

[@mvchowdary377]

Nice video Sir, Can you please tell how to automate the downloading of Reports from Power bi service instead of downloading manually and store in Share point.

[@AmiraJoyBona]

Hello! This is very helpful 🙏🏻 Does this functionality also works for data within SP library or only for SP list?

[@KeaPointTechTips]

Yes, you can connect to document library to fetch metadata too. Just use the SharePoint Online List connection from Power BI and you will see the document library in the list of selections.

[@umangdbz]

V nice

[@KeaPointTechTips]

Thanks

[@arsalanamin2525]

Hi Rashid, It is humble request to would you please guide or make video to show records more than 2 Lists to Power BI Report. Much appreciated your kind help and support.

[@KeaPointTechTips]

That is really easy, you will need to connect Power BI report to multiple lists. If lists are related then create a relationship using Power BI desktop user interface. I will add this topic to me to do videos list.

[@kenpatel3179]

Hello very good video how do you delete PowerBI on sharepoint website under "Visualize the list?

[@mohammadazrinjamsari7541]

Hi, just want to check if you have uploaded the video on how to set a flow in power automate when we update/delete data in sharepoint? Thanks!

[@KeaPointTechTips]

I haven't, sorry.

[@paragbhokre6896]

This helped, however need more advise on how to remove a specific item from the streaming dataset, is it possible?

[@KeaPointTechTips]

I have not tried it, can you explain the business case for it?

[@maheshgupte1495]

This is awesome

[@KeaPointTechTips]

Thank you

[@simnaumesh6396]

Hi, Is there a way where we can create a list in SharePoint with data that has been created powerbi table (dataset) with all calculated columns included using flow? More of the reverse process that has been explained here.

[@KeaPointTechTips]

If you are referring to the dataset in Power BI Service, I'm afraid there is no way to do it with the Power BI connector, it only supports to add a row and refresh data.

[@soumyadeepmondal6130]

Hi! Great video... Just wanted to know can this method be implemented for getting real-time data from MSP Online as well? Thanks!

[@KeaPointTechTips]

Never tried on MPO. I think possible.

[@lobnaamer7440]

i don't understand why we can't use sharepoint list as datasource w. a power automate flow to refresh dataset

[@KeaPointTechTips]

You can, but we can only refresh data 8 times. to achieve near real time we need to adopt this approach.

[@pauloswardblad9590]

Hi Rashed Thank you very much for your demo, it was realy easy to follow along and it works . Big thanks:). I have two issues 1. when i delete a row on the sharepoint list, it still retriving the same data 2. when i update a status (i have status field ongoing, paused, finished) on the sharepoint list, it does not update. I know you mention early in the video about the update. Are you gonna make a video about update, or do you know where i can find information to add this to the power automate?

[@KeaPointTechTips]

Thanks. You will have to write a custom connector to get update on the records and delete it form the data set. I will make a video on it soon.

[@pauloswardblad9590]

@@KeaPointTechTips Thanks. Do you mean by using data/coustom connector/new coustom connector in power automate?

[@vijaykumarbajjuri8220]

Hi, did you get solution for your question , could you share

[@akhils8004]

@@KeaPointTechTips : Please let me know if you have any video for writing a custom connector to get update on the records and delete it from the SharePoint data set. Basically, it will be helpful in Power BI real time reporting from SharePoint list.

[@gashuanley6392]

Great video! Can we create a report using Power BI Service without Power BI Desktop?

[@KeaPointTechTips]

You mean by just using power BI web portal? Yes, you can.

[@nooreldali7432]

Great video!! How can we transform the data? viewing the data in table view is not possible in Power BI, I have a percentage column that I would like to present in percentage but so far it can only be a decimal number (e.g. 0.5 instead of 50%) also I can not remove the thousands delimiter from the column "Year" which I defined it to be a number field in the streaming dataset. Can you help please?

[@KeaPointTechTips]

Check your columns data types in power query. Looks like the data types are not set up correctly.

[@MijalisMendiz]

Hello! Thanks for the video! I'm wondering, is it possible to do it the other way? I mean, create a list in SharePoint from PowerBI data and update the list when the data changes in PowerBI Cheers!

[@KeaPointTechTips]

Hi, Power BI is mainly used for data visualisation. I want to understand the business requirements here.

[@MijalisMendiz]

@@KeaPointTechTips Hi thanks for your answer. It's a temporal workaround, as I need to feed a Microsft Access Database with data from Power BI and I want to use de SharePoint List connector in Access. I know is odd but right now is the only alternative I have.

[@KeaPointTechTips]

You can use PowerApps and connect it directly to Access, or migrate data to SharePoint.

[@TheRemyRomano]

If you update a record will it show up and power VI if you refresh the Power BI page?

[@KeaPointTechTips]

I have mentioned this at the start of video, this only works if you are adding records. For updates you will need to write logic in custom connector.

[@vijaykumarbajjuri8220]

@@KeaPointTechTips Could you make a video on this

[@user-ke4ir6gw8g]

Sir how to integrate MIS with power BI for real time data??

[@KeaPointTechTips]

What MIS system are you trying to connect via Power BI, check if teh connector for Power bi is available for teh targeted MIS.

[@user-qr2er2qc6h]

Is it possible to send the data from power BI to sharepoint list?

[@KeaPointTechTips]

Yes, it is possible via Power Automate flow. I have a video on this topic in my to do list. I will prioritise it in the coming days.

[@vibhavsingh8874]

There are two things that didn't work out for me 1) When I use streaming datasets in a report I'm not able to use any other source. 2) I am not able to create custom columns.

[@KeaPointTechTips]

This example covers only the basics of one list to streaming dataset scenario, You will need some kind of ETL process in the middle to combine data from multiple resources and add any additional columns you may have.

[@kaustubh3269]

hi, if we don't power automate it, and update the list, will it still work ?

[@KeaPointTechTips]

It will only work in Power BI desktop not Power BI online. You will have to click refresh button in Power Bi desktop to refresh the dataset from the source.

[@kaustubh3269]

@@KeaPointTechTips Thank You

[@user-hl6kc5sg8i]

Can i etl bofore load?

[@KeaPointTechTips]

Power BI is not a dedicated ETL tool, but Power BI does come with a built-in user interface to get and transform data using Power Query. Give it a go.

[@pablosaizortuno4740]

It is easier to create with Power Automate; when a new Item is created in the SP list, just refresh the Power BI. Just write it with natural lenguage in the Power Automate and it will create automatiically that flow for you

[@IngeborgHawighorst]

Can you elaborate on that? The Refresh command in Power Automate is not showing the report/dataset I want to refresh. It does not work with all datasets.

[@anwarkrg9292]

​@@IngeborgHawighorstI'm also failed setting scheduke refresh dataset from sharepoint using web connection