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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
nishi
Frequent Visitor

Use Sharepoint Paths in a dynamic table and publish to Power BI Services

Hello everyone,

A couple of years back, I addressed a problem that was resolved with the guidance of @PhilipTreacy which I marked as solved: 

Solved: Re: Text from a specific row based on codintion - Microsoft Fabric Community

 

However, this method does not work when attempting to publish the dataset to Power BI Services, as it is identified as a Dynamic Data Source.

nishi_0-1716219320812.png

 

I previously encountered a similar issue when trying to use an external Rest API but managed to find a workaround using the 'RelativePath' method. Is it also possible to use RelativePath to solve this issue or is there a similar workaround to publish a dataset that includes a table with SharePoint Paths?

 

Snippets below

 

_ENVIRONMENT (parameters)

 

"HML" meta [IsParameterQuery=true, List={"HML", "PROD"}, DefaultValue="HML", Type="Any", IsParameterQueryRequired=true]

 

 

HostSharePoint (table)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vD1UdJRyigpKSi20tdPzs8ryS/O1yvOSCxKLcjPzCvRS87P1S/OLEkt1k/Lz09KLAKqDnDyjHf29wvxD/bXd/P3V4rViVYKCPJ3ocggJ8cgpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Environment = _t, Site = _t, SharePoint = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Environment", type text}, {"Site", type text}, {"SharePoint", type text}})
in
    #"Changed Type"

 

 

SourceSharePoint (table)

 

let
    Source = SharePoint.Files(Table.SelectColumns(Table.SelectRows(HostSharePoint, each [Environment] = _ENVIROMENT), "Site")[Site]{0}, [ApiVersion = 15]),
    SharePoint_Schema = Table.SelectRows(Source, each Text.Contains( [Folder Path] , Table.SelectColumns(Table.SelectRows(HostSharePoint, each [Environment] = _ENVIROMENT), "Site")[Site]{0} &"/"& Table.SelectColumns(Table.SelectRows(HostSharePoint, each [Environment] = _ENVIROMENT), "SharePoint")[SharePoint]{0} &"/"& Table.SelectColumns(Table.SelectRows(HostSharePoint, each [Environment] = _ENVIROMENT), "Environment")[Environment]{0} &"/") )
in
    SharePoint_Schema

 

 

ReadingXLSX

 

let
    Source = SourceSharePoint,
    Base = Table.SelectRows(Source, each [Name] = "sample.xlsx"),
    Base_Sample = Excel.Workbook(Base{0} [Content])
in
    Base_Sample

 

 

2 REPLIES 2
nishi
Frequent Visitor

 
Unfortunately your proposal won't solve my problem.
 
At our company, we have adopted the concept of HOMOLOGATION and PRODUCTION environments, which means that we may have multiple homologation_sharepoint_paths and multiple production_sharepoint_paths. This results in multiple parameter lists, each containing sharepoint paths for both environments.

 

When deploying to production or working on a new release, this consumes a lot of time. Additionally, there is a risk that the person deploying may forget to change the sharepoint path on the parameter list.
 
To address this, we are considering the implementation of a custom table containing the paths and a column for the environment.

 

This would allow us to quickly switch between environments using a single parameter. This approach would streamline our deployment process and minimize the risk of errors.
 
I would appreciate your thoughts on this idea and any suggestions you may have for its implementation. 
 

nishi_0-1716296223819.png

v-xinruzhu-msft
Community Support
Community Support

Hi @nishi 

You can consider to set the url of the sharepoint as a paramater, e.g set the paramater name to 'sharepointsite', then change the code to the following.

let
    Source = SharePoint.Files(sharepointsite,[ApiVersion = 15])
.....
in
...

Then input the sharepoint site url to the paramater directly.

 

Best Regards!

Yolo Zhu

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

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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