Hey @Annika_Cederblad - In general, when needing to create JSON bodies like this, we use a combination of the Insert Column step and the Merge Values step. I took your dummy data and made this sample flow to show you the steps you’ll need:
In my flow, the Pull from Google Sheets step is your Table 1 and Pull from Google Sheets (1) is your Table 2.
First, I take the Table 2 data and create a new column using the Insert Column step:
Then, I use the Merge Values step to merge my items into a single cell per Order No.
Next, I use the Combine Tables step to combine Table 1 and Table 2 together using Order No. as the matching criteria.
Lastly, I use another Insert Column step to create my JSON column:
My output looks like this:
{"Order No":"1000","Date":"8/1/2020","Customer":"Paul","Line Item":[{"Item":"Orange","Price":2.00,"Quantity":5,"Subtotal":10.00}]}
{"Order No":"1001","Date":"8/1/2020","Customer":"John","Line Item":[{"Item":"Orange","Price":2.00,"Quantity":10,"Subtotal":20.00} , {"Item":"Apple","Price":1.00,"Quantity":2,"Subtotal":2.00} , {"Item":"Banana","Price":0.50,"Quantity":9,"Subtotal":4.50}]}
Hope that helps!