What is the best method to import and store data

[Anonymous]

Hello All,

I'm building a scorecard report for a few big projects in the new year. I need to bring in many sources of data to build out the different KPI's being measured. Right now I have a couple of Microsoft Flow's built to import excel files into SharePoint lists, however, I'm wondering if there is a better way. I thought about doing a streaming dataset the same way, but, I can't use multiple streaming datasets and can't add multiple tables to the same set. I'm just trying to use the SharePoint lists like database tables, but, don't know if there is a better way to get this done. 

I know some datasets will likely require key stakeholders to provide their vendor-provided data by appending to Excel files, but I'm trying to automate as much as possible to avoid manual work.

what do you guys use?

[v-rzhou-msft]

Hi @Anonymous 

It is better for you to build your report by power bi desktop and then publish your report on service.

Power BI Desktop is a complete data analysis and report creation tool that you install for free on your local computer. It includes the Query Editor, in which you can connect to many different sources of data, and combine them (often called modeling) into a data model. Then you design a report based on that data model. 

If you want to build a Streaming dataset, you may refer to this blog.

To optmize your data source you may refer to this blog.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hello,

That makes a lot of sense, sorry also, I didn't clarify.

I'm building out a scorecard in Power BI desktop that I will publish to the service for automated refresh and general consumption. When I'm in the desktop I have not been able to use a Power BI dataset AND another data source. Because of this I have to use sources that the service has access to. Right now I'm using:

• SQL Server with gateway
• Files in a Sharepoint Folder
• Sharepoint online Lists

I get some data sources in daily/weekly emails with Excel attachments. I'm taking these and importing them into sharepoint lists which I reference. What I'm looking for are best practices for merging incremental/partial data feeds.

this scorecard will have a few "contributors" on the back-end supplying data that I will have to work with. For some, I will have the users deposit the files to a sharepoint folder. But I'm trying to automate as much as possible.

Thank you,