Dynamic data source refresh with external stored power query m code

[SolarPizza]

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.

[SolarPizza]

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

[SolarPizza]

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.