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

[nishi]

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.

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

[nishi]

Hi @Anonymous,

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. 

[Anonymous]

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.