Forum Discussion
Dynamic data source refresh with external stored power query m code
your text files can also be accessed through a URL that includes query parameters. Try that instead of/in addition to the relative path.
Thank you for your response.
But as i am not very experienced with this, can you please explain this a bit further?
What should i change?
I tried to add the query parameter to the url now, but i still run into the authentication problem:
let
Source = Text.FromBinary(
Web.Contents("https://[companyname].sharepoint.com/[subfolders]",
[
RelativePath= "[txt file name].txt",
Query = [csf="1&web=1"]
]
)
),
EvaluatedExpression = Expression.Evaluate(Source, #shared)
in
EvaluatedExpression
Or run into the dynamic data source refresh error when uploading to power bi web services:
let
RelativePath = "[txt file name].txt"
in
let
Source = Text.FromBinary(
Web.Contents("https://[companyname.sharepoint.com/[subfolders]" & RelativePath,
[
Query = [csf="1&web=1"]
]
)
),
EvaluatedExpression = Expression.Evaluate(Source, #shared)
in
EvaluatedExpression
Maybe it helped a little.
It took me bit but i changed the link to use REST API with GetFileByServerRelativeUrl. I then used the RelativePath again and i did not received the authentication error.
let Quelle = Text.FromBinary(Web.Contents("https://[companyname].sharepoint.com/sites/[library]", [ RelativePath="/_api/web/GetFileByServerRelativeUrl('/sites/[library]/[subfolders]/[txt file name].txt')/$value" ] ), null), EvaluatedExpression = Expression.Evaluate(Source, #shared) in EvaluatedExpression
But i still received the dynamic data source error in power bi services.Then i tried something different.
I removed the last step (Expression.Evaluate) from my query. So the text would be pulled from the txt file on sharepoint but not interpreted and executed as m code. Surprisingly this query worked in power bi services.
The code inside the text file directly uploaded to power bi services works too.
So i assume what causes the dynamic data source error is the Expression.Evaluate function.
Is this possible? If so, do you know a workaround?
I am also open to other solutions.
Have you every find a resolution to this? I seem to have the same issue with Expression.Evaluate. Everything up to that point doesn't cause the dynamic data source issue.
I am attempting to use power query functions referenced from github and the same function as above works find in Desktop, just not when published (can't refresh due to dynamic data source issues).
