Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic data source

Hi, 

 

I have multiple excel files on subsite teams/SharePoint folders. 
I have created a function to get all the files at once but since it is a dynamic data source, it can not refresh in the Service.

Web.Contents /RelativePath method does not work since there is an authentication needed to access the SharePoint folders online.
and to my knowledge Web.Contents is only allowed as anonymous.

Any tips or tricks or is it wait until dynamic data source is available?

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Sorry @Anonymous, but I not sure the Power BI will let you this because of the way the Credential are effectively stored.
Rather than storing a single credential for https://xxxxxxxxxxx.sharepoint.com/ the service will be trying to create one for each https://xxxxxxxxxxx.sharepoint.com/sites/xxxxx-"&Company.  The service may not allow the company to change because it leads to the credential change or the need for new credentials.  New Credentials will require you into the dataset settings to update.

The Web.Contents relative path feature is really for API calls.  It allows the main part of the web address to be stored for credential purposes while allowing the whole web address to change.  I think in your case https://xxxxxxxxxxx.sharepoint.com/sites/xxxxx-"&Company is part of the main address.

Do you really need it to be Dynamic or are you merely trying to create a PBI Template that asks for the new Site.

View solution in original post

10 REPLIES 10
lol123456
New Member

hey, did you solve this issue ? if yes, it would be great to share it

 

v-stephen-msft
Community Support
Community Support

Hi @Anonymous ,


Could you tell me if your problem has been solved?
If it is, kindly Accept the helpfu reply as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.


Best Regards,
Stephen Tao

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Sorry @Anonymous, but I not sure the Power BI will let you this because of the way the Credential are effectively stored.
Rather than storing a single credential for https://xxxxxxxxxxx.sharepoint.com/ the service will be trying to create one for each https://xxxxxxxxxxx.sharepoint.com/sites/xxxxx-"&Company.  The service may not allow the company to change because it leads to the credential change or the need for new credentials.  New Credentials will require you into the dataset settings to update.

The Web.Contents relative path feature is really for API calls.  It allows the main part of the web address to be stored for credential purposes while allowing the whole web address to change.  I think in your case https://xxxxxxxxxxx.sharepoint.com/sites/xxxxx-"&Company is part of the main address.

Do you really need it to be Dynamic or are you merely trying to create a PBI Template that asks for the new Site.

Anonymous
Not applicable

I see what you mean, thanx for the explanation!
The dynamic part is to get all files at once rather than adding the files one by one and the creation of a lot of transform files. 

Thanks @Anonymous - note if you want to get a list of all the files.  Can I suggest using the SharePoint.Contents or SharePoint.Files power query functions.  But I recommend using the Implemention version 2.0.  It is faster and more stable than the older api.

SharePoint.Contents( #"SharePoint Site", [Implementation="2.0"] )
SharePoint.Tables( #"SharePoint Site", [Implementation="2.0"] )
SharePoint.Files( #"SharePoint Site", [Implementation="2.0"] )

 

After this point, you can use the standard table filters in Power Query rather than messing around with URL strings.

Anonymous
Not applicable

List of all files is not possible, to my knowledge, because the files are in different PRIVATE teams channels, ie files are in separate SharePoint subsites. That is why I tried to make the dynamic URL data source funtion.

Michael19842
Helper II
Helper II

Hi @Anonymous, 

 

The Url your using is dynamic. Unfortunately there is no relativePath param using sharepoint.files. You'll have to use web.contents. Please have a look at https://community.powerbi.com/t5/Service/Web-Contents-with-dynamic-file-URL/m-p/928233   

Anonymous
Not applicable

Thx, for the link!

 

Keep getting authentication issues.

Michael19842
Helper II
Helper II

Web.Contents will work. As long as you do not hard set the "Authorization" header, it should be handled by by the engine. RelativePath is the way to go to avoid the dynamic data source warning

Anonymous
Not applicable

What do you mean by "not hard set the authorization header":
My case is:
https://xxxxxxxxxxx.sharepoint.com/sites/xxxxx-Company

Company is the name of the private channels on teams, so this should be dynamic to get all the files from each channel. There are multiple excel files in each channel as well.
My current method to do this(dynamic data source) and works in desktop is:
let
Source = (names as text) =>
let
Source = SharePoint.Files("https://xxxxxxxxxxx.sharepoint.com/sites/xxxxx-"&Company, [ApiVersion = 15])
in
Source
in
Source



 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors