Fix errors in PBI dataflow from exported JSON files

Hello, 

Thank you for getting back to me, and apologies for my delay in responding. 

I am working on extracting social media marketing data for a software company and their competitor companies, using the Brandwatch platform. I am using an consumer research API (Getting Started (brandwatch.com)) in fabric data pipelines following a very similar logic to that within this article Martin's Blog - Working with OAuth 2.0 APIs in Azure Data Factory: Refreshing tokens (martinschoombee.com). 

The below screencap shows the pipeline and a series of web connection/set variable steps using the guidance from the brandwatch.com link, that eventually results in JSON file outputs, which I save into a Bronze Lakehouse, and then into a power bi dataflow for consumption. 

1. I use the pipeline expression builder to extract the username and password (stored as variables) to get the OAuth token for the API connection. This is then set as a variable in the next step of the pipeline
2. I then use the oauth variable set in step 1 to get the project summary using the relative URL in the settings.

    

3. From step 2 I then follow the guidance set out in the Brandwatch.com documentation to get the specific queries that I want from the project. This again is via relative URL links and the oauth variable set in step 1, and the project_id gained from step 2.

4. The specific query names that I want to obtain from the project are static named string variables (filter_array) which I use as dynamic content in the relative url in the "Filter to specific queries" activity step. I filter the results from the previous web step based on the filter array variable. 

    

5. From this I then use a For Each activity using the output of step 4 (filter to specific queries). This contains 4 activities within it (3x set variables and 1x copy data).
6. I set the query_id and query_name based on the output from the previous steps using @string(item().id) and 
   @string(item().name) respectively. The mention_request_url is set following the url link structure in the brandwatch.com guidance documentation, using the project_id, query_id, start and end_date variables I have set in previous steps and '&pageSize=800' as this is the optimum amount of page size I can use without timeout error on the API. I know this from trial and error when running the pipeline.  
7. The copy data activity then uses the output of the mention_request_url variable and a series of advanced factors as shown in the screencap. I believe this may be where I am getting the error due to the pagination rules used to split the JSON files, but I am not 100% sure.

   At the end of the JSON output, there is a nextCursor. The pagination rules in my pipeline use this nextCursor from the previous JSON file from the output of the copy data steps to start off the next lot of pagination, and the EndCondition is when the $nextCursor of the JSON output is blank. Please see the screencap below for an example of this 

   

    

8. The problem arises when I then take the output of the JSON file extractions into the Bronze Lakehouse and put this in a dataflow. I recieve this error when loading the power BI dataflow. 

   I can see from the JSON output that the JSON file doesn't end how PBI would expect it to to be able to correctly format the JSON. I imagine this might be where the error is coming from, but I am not sure. 

   

Please could you help me understand what I need to do either in the data pipeline or in the dataflow to correctly format the JSON output? Thank you in advance for your help, and I hope this is enough detail to help you understand my problem. 

Thanks again! 

L