Sharing and measures issues in a Sharepoint environment

[Billy68]

Hello everybody.

I am turning to you for lack of finding a solution to the following problem: PowerBI doesn't allow me to work efficiently on Excel files positioned on Sharepoint.

I'll explain why: Since I have PowerBI Pro, I can integrate an Excel file deposited on Sharepoint, in a workspace. Once in PowerBI Desktop, I can connect to this data source. Almost perfect, except that it is impossible to create any "Measure", essential if I want to do calculations.

Of course, I can create a PowerBI report with "Measures", as long as my Excel file is on my local PC, on C: or better on "OneDrive", but this has several drawbacks:
o The users with whom I share the report can possibly update the Excel file (thanks to the OneDrive sharing) but will then be blocked to UPDATE the PBI report because the path to the "OneDrive" file is like "c: \ Users \ Billy68 \ OneDrive - MyCorp \ Document \ File004.xlsx "

o If one day I am unavailable, the report will not be able to be refreshed, or even worse, if I am no longer part of the company, this OneDrive space may be deleted and all of its content lost.

The solution of pointing to a Sharepoint "List" is not suitable either because it is limited to 20,000 lines, insufficient to point to our 004 type files. It is also impossible to create any "Measure",

The company I work for doesn't have a file server other than Sharepoint.

I feel stuck: do you have an idea for me?

Sincerely, Michel

[v-deddai1-msft · Accepted solution]

Hi @Billy68 ,

Are you trying to connect to excel file in sharepoint folder? There are build-in connector for it:

Enter in the url like below:

https://partner.onmicrosoft.com/sites/[Your Site Name]/

For more details, please refer to https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/

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

Best Regards,

Dedmon Dai

[Billy68]

Hello !

Thank you very much for your help !

Your detailed procedure, much better comprehensive than the official one, finally allowed me to make a link to my Excel file.

I was able to find my file among the 23,000 present on our Sharepoint space.

All 68,000 lines of the file have been loaded.

I can also create measures.
The update (replace) of the Excel file is going well too.
Be careful though: in Power Query, correctly filter the file, on its full name and in its folder, because if PowerBI finds another file with the same name, it will aggregate the data of the 2 files.
Thanks again !
Yours,
Michel