How to pull the data to Power BI from monthly excels placed in sharepoint automatically

[lavankumar1989a]

Hello Experts,

Hope all are doing great!

need a small assistance on how to pull the data sharepoint excel files and append the data in Power Bi desktop to build the report.

like user places the monthly excel file and power need to pick it up automatically and load into power BI.

is there any solutions or work arrounds, kindly share.

Thanks much for your kind help in advance

Take care and stay safe!

Thanks,

Lavan

[lavankumar1989a]

@amitchandak ,

Thanks much for your prompt response. i guess above solution will not work for my requirement. Let me explain clearly what i am looking,

i want to develop report on top of sharepoint excel files- this i can do

every month user will place new file like monthly basis on sharepoint Jan2021 one excel, feb2021 another excel, mar2021 another one, apr2021 another one and may2021 another excel file.

each file contains 3 sheets in it and structure should be same in all the excel files, i can import all files available now and develop report,

next month user uploads new files for Jun2021, power bi automatically pick and load the data and show the figures for Jun2021 in report level.

Kindly let me know if any clarifications required.

Thanks,

Lavan

[v-xulin-mstf]

Hi @lavankumar1989a,

It is not supported to refresh data from different files placed in sharepoint.

You can refresh a dataset stored on SharePoint Online, but only overwrite the same files.

Please refer: https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-onedrive

Best Regards,
Link

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

[lavankumar1989a]

@v-xulin-mstf ,

Thanks much foryour reply. i knew the solution which you provided as overrite the same file with the latest months data and saving. but, i am looking for different files for monthly and i knew that we don't have a solution and posted here for a work around solutions if any.

Thanks,

Lavan

[v-xulin-mstf · Accepted solution]

Hi @lavankumar1989a,

As a workaround, you can get recent files as the following steps:

Get data from SharePoint folder.

Sorted by created date.

Keep top rows.

You can select one or more recent files.

Refresh data.

You can get the latest file after you create new file.

Best Regards,
Link

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

[amitchandak]

@lavankumar1989a , Power automate can help.

But I am find one flow with power bi paginated report and  sharepoint

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-automate-paginated-onedrive-shar...

Another option is to use the rest APIs

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/exportreport