If I use an excel file as data source, how to insert data back to this file?

[Anonymous]

Dears,

I have an excel file on SharePoint as the centralized data source. and there will have regular new data filess from teammates, I want to put all the new files in a folder and import them in Power BI. I created a new query to get the lines that are not in centralized data source. Then I want to insert these new lines in the cntralized data source.

How can I git it done? 

I know how to insert data to database (access, SQL Server etc.) by using native SQL function. But I don't know how to writed back to excel. 

Open the file and paste the data in could be a solution, but I don't want to let the end users do that way.

I know there is very simple way that we can put all the files in a folder, and using that folder as data source. Yet this approach is very slow once there are a lot of files in the folder. 

What I'm trying to do is to separate data collection process and data visulization process. 

[V-lianl-msft]

Hi @Anonymous ,

Refer to:

https://solutioncenter.apexsql.com/how-to-import-and-export-sql-server-data-to-an-excel-file/ 

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

[Anonymous]

Hi, Liang, Thanks! Seems the article is about how to export data to excel in SQL server. I'm looking for the way to write back data to excel from Power BI. It will be highly appreciated if you could help on that. Thanks a lot.

[MFelix · Accepted solution]

Hi @Anonymous ,

what you need is to use the Power BI alongside with PowerApps.

Check this small example and the community part that is dedicated to the better together.

https://www.sharepointeurope.com/integrating-powerapps-power-bi-and-flow-to-create-realtime-streaming-applications/

https://community.powerbi.com/t5/Power-Platform-Integrations/bd-p/pbi_pp_integrations

[Anonymous]

Hi, Meguel,

Thanks for your help and sorry for my late reply.

Yes, use Power Apps could be a solution. Yet it also makes things more complicated.

Seems that it is not a good idea to update excel file by power BI. I may need do it by vba or something else, and just let power BI deal with data analysis and presenting.

Again, thanks for your help!

[MFelix]

Hi @Anonymous , 

If your files are all in the same format the best option is to use the sharepoint folder connection.

check the link below with the complete details.

https://powerbi.microsoft.com/en-us/blog/combining-excel-files-hosted-on-a-sharepoint-folder/

If you need further assistance please get back to me.