How to fix the error on "One or more tables references a dynamic data source" on Power Bi Data Flow?

[bikelley]

Hello,

I trying to create data flow with API on Power BI online, but I am running into an error called "One or more tables references a dynamic data source".

Query

let
Source = Json.Document(Web.Contents("https://rally1.rallydev.com/slm/webservice/v2.0/defect?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/331029809496&query=&fetch=true&start=1&pagesize=20000", [Headers=*****************************************"]])),
QueryResult = Source[QueryResult],
TotalResultCount = QueryResult[TotalResultCount],
DynamicList = List.Generate(() => 2000, each _ < TotalResultCount, each _ + 2000),
FinalList = List.Combine({{1},DynamicList, {TotalResultCount}}),
#"Converted to Table" = Table.FromList(FinalList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "StartPage"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Json.Document(Web.Contents("https://rally1.rallydev.com/slm/webservice/v2.0/defect?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/331029809496&query=&fetch=true&pagesize=2000&start="&[StartPage], [Headers=[******************************"]]))),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"QueryResult"}, {"QueryResult"}),
#"Expanded QueryResult" = Table.ExpandRecordColumn(#"Expanded Custom", "QueryResult", {"Results"}, {"Results"}),
#"Expanded Results" = Table.ExpandListColumn(#"Expanded QueryResult", "Results"),
in
#"Expanded Results"

Error

I try to break the query into different tables did not work.

let
  Source = Json.Document(Web.Contents(https://rally1.rallydev.com/slm/webservice/v2.0/defect?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/331809496&query=&fetch=true&start=1&pagesize=20000, [Headers=**********************"]])),
  QueryResult = Source[QueryResult],
  TotalResultCount = QueryResult[TotalResultCount],
  DynamicList = List.Generate(() => 2000, each _ < TotalResultCount, each _ + 2000),
  FinalList = List.Combine({{1},DynamicList, {TotalResultCount}}),
  #"Converted to Table" = Table.FromList(FinalList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
  #"Converted to Table"

2nd Table

let
  Source = dynamic_List,
  #"Converted to list" = Source[Column1],
  #"Converted to table" = Table.FromList(#"Converted to list", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Changed column type" = Table.TransformColumnTypes(#"Converted to table", {{"Column1", type text}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed column type", {{"Column1", "StartPage"}})
in
  #"Renamed Columns"

3rd table - This will give me the above error, did not give me to save it. 

let
  Source = dynamicList_expand,
  #"Added Custom" = Table.AddColumn(Source, "Custom", each Json.Document(Web.Contents(https://rally1.rallydev.com/slm/webservice/v2.0/defect?workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/331029809496&query=&fetch=true&pagesize=2000&start=&[StartPage], [Headers=[************************************]]))),
  #"Transform columns" = Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Custom", null}})
in
  #"Replace errors"

Can anybody please tell me how to fix this or work around this?

Thank you so much

[bikelley]

@ibarrau 

Thank you so much for the solution. The only issue is on my API I have a 2000 raw limit I can pull. That is why I have a table with a staring page, but I am not sure how to implement that on your solution. 

You can see below startpage given according to the below table. 

Any idea, how to do this dynamically? 

Thank you  

[ibarrau]

You can add a new column to that table you are showing with the web conent code. You will change the code to match start with the StartPage column.

"start"=[StartPage]

The new column should return a table you can expand to get the results. You can follow this post: https://blog.ladataweb.com.ar/post/188215249200/powerquery-web-content-por-fila

You may want to create a custom function with the request or any additional transformation. It would be a nice idea to keep the code clean. That way you can just add a new column calling that function and everything from the content, expand columns and transformations might be solved. (This is just up to you)

I hope that helps

[bikelley]

Hi @ibarrau 

Onle last thing and so sorry to be a pest. I have very little knowledge of API and Power Query and I am trying to figure out piece by piece. From what I understand, I tried the following code. This does not give me the dynamic error, but I am not getting data. Do you think the direction I am going is correct? 

let
  Source = Web.Contents(
            https://rally1.rallydev.com/, 
            [ 
              RelativePath="slm/webservice/v2.0/defect",
              Query= [
                workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/331809496
                , query=""
                , fetch="true"
                , start="1"
                , pagesize="2"
              ],
              Headers=[“*******************************"]
            ]
),
  jsonResponse = Json.Document(Source),
  Navigation = jsonResponse[QueryResult],
  TotalResultCount = Navigation[TotalResultCount],
  DynamicList = List.Generate(() => 2000, each _ < TotalResultCount, each _ + 2000),
  FinalList = List.Combine({{1},DynamicList, {TotalResultCount}}),
  #"Converted to Table" = Table.FromList(FinalList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type text}}),
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "StartPage"}}),
  #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Web.Contents(
            https://rally1.rallydev.com/, 
            [ 
              RelativePath="slm/webservice/v2.0/defect",
              Query= [
                workspace=https://rally1.rallydev.com/slm/webservice/v2.0/workspace/331809496
                , query=""
                , fetch="true"
                , start="StartPage"
                , pagesize="2000"
              ],
              Headers=[“****************************”]
            ]
)),
  #"Transform columns" = Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Custom", null}})
in
  #"Replace errors"

Thank you so much for your time. 

[ibarrau]

Wel I don't know why you are calling two times the API (I can't understand your source step). I thought you were staring the query with a source like "StartPage" with 5 rows. Then "Add custom column" with the API call using the result of the small table as parameter for the API to call it many times like a "loop".

Be carefull with 

start="StartPage"

 If you use it like that you are hardcoding the value "StartPage" the text in the param. You don't want that, you want the value of the column. You need to user in brackets the column name like [StartPage].... if it shows an error for being a number you can convert it to text in the same code like Text.From([StartPage]).

I hope that helps because now I feel like we are running on circles and can't test it 😛

[ibarrau · Accepted solution]

Hi, the only way I know that might be able to help you with the dynamic source is improving the parameters in the Web.Contents like this: https://blog.ladataweb.com.ar/post/630597294839955456/powerquery-buena-práctica-para-un-web-request

It's a good idea to separate the relative path and query (params) from the url to help Power Bi understand that the source is just a simple url like: https://rally1.rallydev.com/

You can play with the parameters, I'm not sure if all is a single "workspace" parameter or if they are five different like the following:

Web.Contents(
	"https://rally1.rallydev.com/", 
	[ 
		RelativePath="slm/webservice/v2.0/defect",
		Query= [
			workspace="https://rally1.rallydev.com/slm/webservice/v2.0/workspace/331809496"
			, query=""
			, fetch="true"
			, start="1"
			, pagesize="20000"
		],
		Headers=**********************
	]
)

That's an example of what I'm saying. You can play with it to check if the relative path should start from slm or just use "defect" adding the rest on the regular url. Also check the query params because it's not clear if it's just a workspace param or 5 of them. I haven't worked with an url which has another url as param so that's confusing.

I hope that helps,