cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Populate Excel table with JSON data

Hi guys,

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:

{
  "stores: [
    {
      "brand": "Couche",
      "code": "23911",
      "name": "Couche Rosebery",
      "address": "21 Gardener Rd, ROSEBERY NSW 2008"
    },
    {
      "brand": "Couche",
      "code": "16909",
      "name": "Couche Alexandria",
      "address": "122 Wyndham Street, ALEXANDRIA NSW 2055"
    },
    {
      "brand": "Couche",
      "code": "17253",
      "name": "Couche Leumeah",
      "address": "62 Rudd Rd, LEUMEAH NSW 2560"
    }
  ],
  "prices": [
    {
      "scode": "23911",
      "type": "Suit",
      "price": 125.7
    },
    {
      "scode": "23911",
      "type": "WDress",
      "price": 139.7
    },
    {
      "scode": "23911",
      "type": "Tux",
      "price": 145.7,
      "lastupdated": "28/02/2020 04:45:02"
    }
  ]
}

 

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.

image.png

 

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?

17 REPLIES 17
Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi @Naz 

 

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:

body('Parse_JSON_2')?['prices'] is not equal to null

 

image.png

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: Populate Excel table with JSON data

@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 😞

Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi Naz,

 

Thanks for your quick reply. Could you check your run history steps. Make sure data exists before it pass to For each loop? 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: Populate Excel table with JSON data

@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?)

Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi Naz,

 

Also could you please post a screenshot of your flow run history?

 

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi @Naz 

 

Could you post a screens shot of your flow? Believe the mapping is wrong.

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: Populate Excel table with JSON data

image.png

 

Here's what I see: 

 
Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi @Naz 

 

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. 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: Populate Excel table with JSON data

@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?

Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi @Naz,

 

Could you post a screenshot of your Excel mapping?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: Populate Excel table with JSON data

@abm, here's the screenshot. Have shown you only one variable, but all of them have been mapped in the same manner.

 

image.png

 

Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi @Naz 

 

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.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi @Naz 

 

Hope you resolved your issue. If you need any further help let me know.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: Populate Excel table with JSON data

Hi @abm , Unfortunately, I cannot split the data. 

 

The issue still persists.

Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi @Naz 

 

 

Thanks for your reply. Its difficult to analyse without the data. Can we do a screen share?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

Re: Populate Excel table with JSON data

 Hi @abm ,

I re-created the flow and the issue is gone!! Wish I knew why. 😞

Highlighted
Super User III
Super User III

Re: Populate Excel table with JSON data

Hi @Naz 

 

Thanks for the update. Glad to hear that its working as expected.

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Helpful resources

Announcements
Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Top Solution Authors
Top Kudoed Authors
Users online (6,549)