Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Dynamic data source refresh with external stored power query m code

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.

Need Help

15 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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