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

     

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