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

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.

ferryv's avatar
ferryv
Resolver II
2 years ago

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's avatar
    lbendlin
    Super User
    2 years ago

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

    • ferryv's avatar
      ferryv
      Resolver II
      2 years ago

      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's avatar
        lbendlin
        Super User
        2 years ago

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

         

        https://raw.githubusercontent.com/