Forum Discussion

TK12345's avatar
TK12345
Resolver II
3 years ago
Solved

Help with syntax -'One or more table references a dynamic Data Source. Not able to save dataflow'

Hi there, 
I have an API that I want to paginate in a Power BI Dataflow. I tried this code in the desktop (dataset) and it refreshes fine. It also shows data in the dataflow but when I close the dataflow I get the error message: One or more table references a dynamic Data Source. Not able to save Dataflow. 

So I have the followings queries/functions/parameters in the dataflow:
1 parameter where I provide my BaseURL              (https://www.cannotshow.nl)

1 parameter where I provide my Bearer token        (Bearer abcdefgheijklmnop)
1 Function called TEST:

 

(currentPage as number) =>

let
    Source = Json.Document(Web.Contents(BaseURL,
                                        [RelativePath ="/test.test/rest/all/V1/orders?searchCriteria[pageSize]=10&searchCriteria[currentPage]="&Number.ToText(currentPage)] &
                                        [Headers=[Authorization=Authentication]]))

in
   Source

1 query that shows me the data:

let
  Source = List.Generate(()=>
[Result= try TEST(0) otherwise null, currentPage=0],
each [Result]<>null,
each [Result= try TEST([currentPage]+1) otherwise null, currentPage=[currentPage]+1],
each [Result]),
  #"Geconverteerd naar tabel 1" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Uitgevouwen Column1" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel 1", "Column1", {"items"}, {"items"}),
  #"Uitgevouwen items" = Table.ExpandListColumn(#"Uitgevouwen Column1", "items"),
  #"Uitgevouwen items 1" = Table.ExpandRecordColumn(#"Uitgevouwen items", "items", {"weight"}, {"weight"})
in
  #"Uitgevouwen items 1"

So the question is:

WHY do I get the Can't save dataflow error? I can see the data is loaded in. Some people with the same issues fixed it by using RelativePath, but in my case it still does not work. What do I need to change in the code so my dataflow will save and refresh. 

Do I have to change something in the Relative Path or what do I need to do? Who could help me out?

 

Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    3 years ago

    Put the query pairs into the Query section, not the Relative path section.

  • TK12345's avatar
    TK12345
    3 years ago

    Thanks, that did the trick.

    Solved it like this:

    (Page as number) as table=>

    let
      Source =
      Json.Document(
        Web.Contents("https://www.cannotshow.nl",
        [RelativePath = "/test.test/rest/all/V1/orders",
        Query = [
          #"searchCriteria[pageSize]" = Text.From(10),
          #"searchCriteria[currentPage]" = Number.ToText(Page)
          ],
          Headers=[
           #"Authorization" = "Bearer abcdefgheijklmnop"
        ]])

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    You specified the query as part of the RelativePath.  Try specifying it separately as the documentation suggests.

    • TK12345's avatar
      TK12345
      Resolver II
      3 years ago

      Hi Ibendlin, 

      Thanks for your reply, I thought I did what the documentation suggests. What do you mean I specified the query as part of the RelativePath, and how do you suggest to change it?

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        Put the query pairs into the Query section, not the Relative path section.