Im trying to populate an Excel table with JSON data. I can successfully run the HTTP request and Parse JSON actions.
Sample of JSON obtained:
I want to populate all the information in the "Prices" into an Excel table.
So I added an "Apply to each", select Output as Prices, have an Add row to table and have the JSON element mapped across the column name.
However, it throws an error stating " the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['body']?['prices']' is of type 'Null'. The result must be a valid array."
Would you happen to know what I am doing wrong here?
The sample data you generate to use the schema have prices array details. Believe what's happening here is the data you testing having prices value null hence you getting the null error.
Add an IF condition to check this array is null or not. The expression I used is as follows:
@abm, I did check that, but it is not the case. The "Output>Body" section of the ParseJSON has data in it. I've copied the data, saved it as a JSON file and imported it into Excel and it works 😞
@abm, Yes, that's what I have done to figure out that data does exist in the Parse_JSON output.
I think the error is to do with the output format of Parse JSON, which the Add row function is not accepting as an input (not formatted as an array?)
You mentioned about add row function is not accepting as an input (not formatted as an array?). So the failure is happening in your Add row Excel step? If then make sure all mappings are correct.
@abm, yes. the failure is at the add row step.
Mappings are correct. I've picked the variables directly from the Dynamic content and they're the same as the content under "prices" in the earlier screenshot.
Just to clarify, the "Prices" section of the JSON contains an array with 4 variables (multiple entries). I am referring to the variables under Prices. Is that correct?
Your mapping looks good to me. The error you are getting is related to data quality issue. How any rows of data getting processed correctly? Could you split the load and see which exact data is failing? In my lunch break I could do a screen share if required. Please let me know.
Learn how to create your own user groups today!
Check out the new Power Platform Community Connections gallery!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.