Forum Discussion
Dynamic data source refresh with external stored power query m code
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.
After some searching online, I found a way to get this resolved.
Since #shared contains all functions, you can specify the individual functions used as a record in the environments part of the Expression.Evaluate function.
For instance:
let convert = Expression.Evaluate(Text.FromBinary(
Web.Contents(
"https://raw.githubusercontent.com/...."
)
), [
#"Table.AddColumn" = Table.AddColumn,
#"Text.BetweenDelimiters" = Text.BetweenDelimiters,
#"Text.Replace" = Text.Replace,
#"Record.Field" = Record.Field,
#"Text.AfterDelimiter" = Text.AfterDelimiter,
#"RelativePosition.FromEnd" = RelativePosition.FromEnd,
#"Text.BeforeDelimiter" = Text.BeforeDelimiter
]
) in convertThis actually allows the refresh to work.
Update: Functions, like Web.Contents, Web.BrowserContents, etc are not part of #shared. But in your case you should be able to just add
[ Text.FromBinary = Text.FromBinary ]instead of #shared that might resolve your issue (in combination with the RelativePath and Query sections in Web.Contents). The only part of Web.Contents that must be static is the domain itself.
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.
yep - that's actually what I do - just used a shorter list for the sake of brevity. I have a text file with them in and just copy into a query. The issue there is that occasionally it needs to be updated as new PQ functions arrive, would be nice if it just worked using #shared in the service like it does in PBI desktop.
My use case is a custom function library - if #shared worked in the service then it's easy enough to share custom functions =Expression.Evaluate(<extract text file with functions in>, #shared). Because it doesn't, the query to import them is thousands of lines long due to that record.
Easy enough for me to do but for newer users I want to shared it with, a 3000 line query is a bit intimifating
