Solution for Power BI reports using Sharepoint list as data source?

[Anonymous]

I'm working on a company that has several Power BI reports launched in the report server and they all have data refresh that works great because all the data sources are inside the local network (//company/files/etc).

I'm building a brand new system that uses Sharepoint lists as the main database and powerapps as a way to manipulate this database by adding and removing items from those lists. So all data is in there.

After ceating a new power bi desktop report using it as the data source, it all went fine. But then I figured out it does not work at all when uploaded to the report server. After reading a bit the documentation it is clear and it does not support auto refresh or whatever for sharepoint lists sources, so it won't work.

I've been digging the internet to find the best solution but I just cant find any.

I have thought of using the Power Automate flows to create a schedule to download all the lists in the sharepoint list to the local network and then use this as the datasource instead, but I'm stuck with this "on-premise gateway", no idea how it works.

Does anyone know a way to work around it?

[v-deddai1-msft]

Hi @Anonymous ,

Would you please mark your reply as answer? 

Thank you for your sharing.

Best Regards,

Dedmon Dai

[Anonymous · Accepted solution]

I kinda of answered my own question on the post and didn't realized it. 

I'll just explain it all if anyone ever has this same issue again:

Sharepoint is kind of an online database so it's all in the cloud, when you use Power BI Desktop you'll need to login with the same account you have permission to that sharepoint list, so it'll be able to authenticate and get all the info you need.

However, it's not that simple when your report is published online. In a Report Server you can upload a report but it won't ever be able to refresh and autocollect the data again once it's there, you'd have to re-upload the .pbix with the data already on it. 

The work around I've found for my company was to create a flow using Power Automate and the on-premises gateway. The way it works is that periodically (in my settings it's once per day) it will fetch all the lists I want that are on the cloud and place it in a folder of my company intranet. This way I've modeled the B.I to gather data from those local networks instead of the online sharepoint list, and once it's in the Report Server it'll work just as fine.

I hope it could be usefull for someone. Cheers.