Forum Discussion
Dynamic data source refresh with external stored power query m code
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).
Expression.Evaluate is by definition a dynamic data source and will never be refreshable on the service.
It's the #shared which is the dynamic source. Unfortunately I haven't found a way around it other than passing a manually created record containing the PQ functions in your queries that are being evaluated.
So, let's say you're using a bunch of List operations, create a record like:
_pq = [ List.NonNullCount = List.NonNullCount, List.MatchesAll = List.MatchesAll, List.MatchesAny = List.MatchesAny, List.Range = List.Range, List.RemoveItems = List.RemoveItems, List.ReplaceValue = List.ReplaceValue, List.FindText = List.FindText, List.RemoveLastN = List.RemoveLastN, List.RemoveFirstN = List.RemoveFirstN ]
and pass it to the Expression.Evaluate line:EvaluatedExpression = Expression.Evaluate(Source, _pq)That's the standard way of declaring scope. Nothing's really stopping you from harvesting all the functions in #shared and stuffing them into your record.
