Forum Discussion
Dynamic data source refresh with external stored power query m code
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).
