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)