Dynamic data source refresh with external stored power query m code

[Anonymous]

Hi all,

We started storing our power query m code inside text files (as Chris Webb explained it here: https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/) and over time created something like a library to ensure everybody is using the same output.

The code to access and execute the external stored m code works fine in power bi desktop, but doesn't when published to power bi services. It's the well known "dynamic data source refresh"-error.

I am aware of the RelativePath, but unfoturnately i have some problems with this.

Here is an example of the original code we use:

let
    Source = Text.FromBinary(Web.Contents("https://[companyname].sharepoint.com/[subfolders]/[txt file name].txt")),
    EvaluatedExpression = Expression.Evaluate(Source, #shared)    
in
    EvaluatedExpression

When i split it with RelativePath, the code looks like this:

let
    Source = Text.FromBinary(Web.Contents("https://[companyname].sharepoint.com/",
        [RelativePath = "[subfolders]/[txt file name].txt"]
    )),
    EvaluatedExpression = Expression.Evaluate(Source, #shared)    
in
    EvaluatedExpression

But then i run into an authentication error in power bi desktop (organizational login). I already have reset the global data source settings and used a different browser for the login pop-up, but this didn't help.

Using it that way, it's working in power bi desktop again. But i also get the error in power bi service again:

let
    RelativePath = "[subfolders]/[txt file name].txt"
in

let
    Source = Text.FromBinary(Web.Contents("https://[companyname].sharepoint.com/" & RelativePath)),
    EvaluatedExpression = Expression.Evaluate(Source, #shared)    
in
    EvaluatedExpression

I guess i am doing something wrong here, but i can't figure out what.

Would be glad to get some help to make it work.

[lbendlin]

your text files can also be accessed through a URL that includes query parameters.  Try that instead of/in addition to the relative path.

[Anonymous]

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

[lbendlin]

see if this is helpful

Working with folders and files with REST | Microsoft Learn

[Anonymous]

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.

[BenMA]

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)

[lbendlin]

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.

[BenMA]

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

[ferryv]

I 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). 

[ferryv]

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).  

[lbendlin]

Expression.Evaluate is by definition a dynamic data source and will never be refreshable on the service.

[ferryv]

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 convert

This 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. 

[lbendlin]

What is listed in the Data Source dialog?  Just this?

https://raw.githubusercontent.com/

[ferryv]

That would be the url to the function you're referencing from github, i.e. a hardcoded url.

[lbendlin]

That wouldn't be a dynamic source then? Well, I guess "dynamic"  is up for discussion here as you can indeed change the text inside the .pq file. Just make sure all used functions are declared in the environment.

[ferryv]

The issue was actually the #shared environment that caused the issue. Even when you hardcoded the url.