Forum Discussion

Rallmo's avatar
Rallmo
Helper I
1 year ago
Solved

Unable to create dataflow due to referencing a dynamic data source

Hi

In my CMDB I have a table containing Application assets, these assets are linked to other things like servers, other applications and so on. With an API call I'm able to gather information about what different assets my Applications are linked to with the following code.

 

 

let
    Source = OData.Feed("https://www.topdesk.net/services/reporting/v2/odata", null, [Implementation="2.0"]),
    AssetApplicationList_table = Source{[Name="AssetApplicationList",Signature="table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(AssetApplicationList_table,{"Id", "ObjectId"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "Relatioship", each Json.Document(
  Web.Contents(
    Text.Combine({"https://www.topdesk.net/tas/api/assetmgmt/assetLinks?sourceId=",[Id]}))
)),
    #"Expanded Relatioship" = Table.ExpandListColumn(#"Added Custom1", "Relatioship")
in
    #"Expanded Relatioship"

 

 

It works fine both on my client and if I create a dataflow and add the code, but as soon as I try to save the dataflow I get the following error:

"One or more tables references a dynamic data source."

 

Searching here I've found a lot of different threads and many refer to Chris Webb's article Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI (crossjoin.co.uk)

Would that be the way forward in solving this issue? If yes, could anyone help me decipher how to use his code/article togheter with my code?

  • Rallmo's avatar
    Rallmo
    1 year ago

    I wasn't able to decipher how to use the documentation to solve my issue, but I found this YT video from BI Elite that was helpful in understanding how i could use RelativePath. Making sure your Power BI web data source refreshes online (RelativePath, Query, BrowserContents)
    I thought that I had to use Query as an input to use RelativePath and that's what I got stuck on.

     

    So for anyone experiencing similar issues, this is the bit of code that solved it for me. 

    Json.Document(
          Web.Contents(
              "https://www.topdesk.net/tas/api/",
              [RelativePath = "assetmgmt/assetLinks?sourceId="&[Id]]
            )
          )

     

4 Replies

    • Rallmo's avatar
      Rallmo
      Helper I

      I wasn't able to decipher how to use the documentation to solve my issue, but I found this YT video from BI Elite that was helpful in understanding how i could use RelativePath. Making sure your Power BI web data source refreshes online (RelativePath, Query, BrowserContents)
      I thought that I had to use Query as an input to use RelativePath and that's what I got stuck on.

       

      So for anyone experiencing similar issues, this is the bit of code that solved it for me. 

      Json.Document(
            Web.Contents(
                "https://www.topdesk.net/tas/api/",
                [RelativePath = "assetmgmt/assetLinks?sourceId="&[Id]]
              )
            )

       

      • lbendlin's avatar
        lbendlin
        Super User

        The recommended approach is to use the Query parameter for query values.  There are some corner cases where Power Query encodes the full URL wrongly, in those scenarios including the query values in the RelativePath is an acceptable option.