Fix errors in PBI dataflow from exported JSON files

[lbg-sage]

I am using Microsoft Fabric Data pipelines to export JSON files into a dataflow using an API connection.

The output of the "Copy Data Activity" exports the data as a JSON file to a Bronze Lakehouse. This "Copy Data Activity" includes the Pagination rules shown in the attached image below.

The Bronze Lakehouse is the datasource for the Dataflow. The pipeline runs successfully up until I attempt to transform in Power Query within the dataflow. 

The error from the dataflow is as follows

The transform file source shows the JSON export to be split like this, which I imagine is what is causing the error. 

Power query isn't formatting the JSON output correctly as the JSON files aren't separated by each row. This would explain the character error shown above.  

Could you please help me figure out how to correctly format the JSON outputs in my power bi dataflow and/or the fabric data pipeline? 

Thank you!

[lbg-sage]

Hey @v-shex-msft Fix errors in PBI dataflow from exported JSON files  any ideas for this one? Still struggling to remove the error. If you have any advice it would be hugely appreciated. I have tried to explain as much as possible above, but please let me know if there is any further information you require and I will do my best to fill in the gaps. 

Thank you in advance for your help!

L

[lbg-sage]

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 (martinschoombe...

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

[v-shex-msft]

Hi @lbg-sage ,

Can you please share some more detail information about this issue? They should help us clarify your scenario and test to troubleshoot.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[lbg-sage]

Hey @v-shex-msft - any ideas on this one?

[lbg-sage]

Hello 

Have you had a chance to look at the additional information I have provided above? 

Thanks,

L