Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
18 | |
10 | |
9 | |
9 | |
8 |
User | Count |
---|---|
17 | |
16 | |
14 | |
12 | |
12 |