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.
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 intimifatingI do the same now also and typically try to limit the operations to the ones I use. It would perhaps be a nice feature for the power query functionality if there was an "default" list that you could call via a keyword similar to #shared (like #default) that eg only contains a list of standard operations (not custom ones).
