cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Naz
Advocate II
Advocate II

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
abm
Super User
Super User

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 blogPower Automate Video Tutorials
Naz
Advocate II
Advocate II

@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
Super User
Super User

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 blogPower Automate Video Tutorials
Naz
Advocate II
Advocate II

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

abm
Super User
Super User

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 blogPower Automate Video Tutorials
abm
Super User
Super User

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 blogPower Automate Video Tutorials
Naz
Advocate II
Advocate II

image.png

 

Here's what I see: 

 
abm
Super User
Super User

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 blogPower Automate Video Tutorials
Naz
Advocate II
Advocate II

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

abm
Super User
Super User

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 blogPower Automate Video Tutorials
Naz
Advocate II
Advocate II

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

 

image.png

 

abm
Super User
Super User

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 blogPower Automate Video Tutorials
abm
Super User
Super User

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 blogPower Automate Video Tutorials
Naz
Advocate II
Advocate II

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

 

The issue still persists.

abm
Super User
Super User

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 blogPower Automate Video Tutorials
Naz
Advocate II
Advocate II

 Hi @abm ,

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

abm
Super User
Super User

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 blogPower Automate Video Tutorials

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (1,796)