Power Platform Community Forum Thread Details

I need som help to figure out the best way to create a CSV file of this array structure.

[
  {
    "PersonId": "1",
    "Name": "Name1",
    "PersonNumber": "0101",
    "Enrollments": [
      {
        "StartDate": "2000-01-01",
        "EndDate": "2000-02-01",
        "EnrolledLocation": {
          "LocationId": "123",
          "LocationName": "LocationA"
        },
        "Type": "work"
      }
    ],
    "Phone": "333333"
  },
  {
    "PersonId": "2",
    "Name": "Name2",
    "PersonNumber": "0102",
    "Enrollments": [
      {
        "StartDate": "2000-01-02",
        "EndDate": "2000-02-02",
        "EnrolledLocation": {
          "LocationId": "123",
          "LocationName": "LocationA"
        },
        "Type": "work"
      },
      {
        "StartDate": "2000-01-03",
        "EndDate": "2000-02-03",
        "EnrolledLocation": {
          "LocationId": "124",
          "LocationName": "LocationB"
        },
        "Type": "STUFF"
      }
    ],
    "Phone": "333333"
  }
]

This array is an example.

I want to create a flat structure so i can generate a csv file.

Structure like this:

{
PersonId:
Name:
PersonNumber:
StartDate:
EndDate:
LocationId:
LocationName:
Type:
Phone:
}

So from above example it should be 3 objects created.
PERSON 1 have one enrollments
PERSON 2 have two enrollments
Total 3 objects in the flat structure.

The problem is that i do not want to use apply-to-each.
It generates way to much traffic if i need to loop all my data to do this.

My dataset is very large and i want to find a way to do this without loops (append to array).

Any ideas on a method to do this. I have not found a way to work with Select, Join, Union etc to make this happen in a better way.
I need some expert to teach me.

Categories:

Building flows

Hi @Oskarkuus

Try this approach - no loops used here :)

1. Used a compose action to store the input json

2. use select action to do required conversion

Expression:

json(xml(replace(replace(replace(replace(string(xml(json(concat('{"Root" :',string(item()),'}')))),'<Enrollments>',concat('<Enrollments><PersonId>',item()?['PersonId'],'</PersonId><Name>',item()?['Name'],'</Name><PersonNumber>',item()?['PersonNumber'],'</PersonNumber>')),'<EnrolledLocation>',''),'</EnrolledLocation>',''),'</Enrollments>',concat('<Phone>',item()?['Phone'],'</Phone></Enrollments>'))))?['Root/Enrollments']
 
quite complex expression - but let me explain in simple way what it does
 
This expression loops through each item /person, convert it to xml and add the required property to enrollments which we need in final, and format it way how it looks in final. Would recommend to have a look into the expression in detail to get idea around this.
 
3. Next use a compose action to convert to required json format
 
Expression used: 
json(concat('[',replace(replace(string(body('Select')),'[',''),']',''),']'))
 
See how the final output looks like after transformation
 
I hope this is what you have expected
 
Thanks & Regards,

Nived N
Stay connected:

LinkedIn | YouTube | Blogs
Was this answer helpful?

If yes, please mark it as the solution by selecting the checkbox in the discussion thread.

Your feedback motivates me to keep contributing. Thank you!