cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luka84
Helper V
Helper V

Extract Nested Collection

Good day,

After a few hours and many, many approaches based on previous post solutions- I give up and will definitely need help with this=)

I have a collection storing several Charge Codes, and for every charge code there are several corresponding service, however this embedded in a nested table 3 levels from the first table.

All I need to do is to create a final collection storing all the services corresponding to a each charge (Looping throughout the table and extracting the information then amending to the collection)

 

Example:

ChargeCodeServiceCode
CH0048Cargo DueSV0045Land Freight
CH0048Cargo DueSV0068Sea Freight
CH0049Cargo Transfer FeeSV0045Land Freight
CH0049Cargo Transfer FeeSV0045Sea Freight

 

Attached screens for reference

Any help at this stage is truly appreciated

 

Thanks

Luka

1 ACCEPTED SOLUTION

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

Hi @Luka84

Based on the issue that you mentioned, do you want to create a table including all the values from the nested table?

Could you please share a bit more about your scenario?

For that I do not know how you create your collection, so I created a collection with similar structure as yours.

ClearCollect(
    nestedCollection,
    {
        Title: "Level 1A",
        Level2: {
            Title2: "Level 2A",
            Level3: [
                {
                    Title3: "Level 3A",
                    Code: "Land Freight"
                },
                {
                    Title3: "Level 3B",
                    Code: "Sea Freight"
                }
            ]
        }
    },
    {
        Title: "Level 1B",
        Level2: {
            Title2: "Level 2B",
            Level3: [
                {
                    Title3: "Level 3B",
                    Code: "Land Freight"
                },
                {
                    Title3: "Level 3B",
                    Code: "Sea Freight"
                }
            ]
        }
    }
)

Here is what my collection looks like:

GIF002.gif

And all you need to do is ForAll twice as below:

ForAll(
    nestedCollection,
    ForAll(
        Level2.Level3.Value,
        Collect(
            Testttt,
            {
                Charge: Title,
                Code1: Level2.Title2,
                Service: Value.Code,
                Code2: Value.Title3
            }
        )
    )
)

2021-03-23-03.png

Hope it could help.

Regards,

Qi

 

Best Regards,
Qi

View solution in original post

2 REPLIES 2
v-qiaqi-msft
Community Support
Community Support

Hi @Luka84

Based on the issue that you mentioned, do you want to create a table including all the values from the nested table?

Could you please share a bit more about your scenario?

For that I do not know how you create your collection, so I created a collection with similar structure as yours.

ClearCollect(
    nestedCollection,
    {
        Title: "Level 1A",
        Level2: {
            Title2: "Level 2A",
            Level3: [
                {
                    Title3: "Level 3A",
                    Code: "Land Freight"
                },
                {
                    Title3: "Level 3B",
                    Code: "Sea Freight"
                }
            ]
        }
    },
    {
        Title: "Level 1B",
        Level2: {
            Title2: "Level 2B",
            Level3: [
                {
                    Title3: "Level 3B",
                    Code: "Land Freight"
                },
                {
                    Title3: "Level 3B",
                    Code: "Sea Freight"
                }
            ]
        }
    }
)

Here is what my collection looks like:

GIF002.gif

And all you need to do is ForAll twice as below:

ForAll(
    nestedCollection,
    ForAll(
        Level2.Level3.Value,
        Collect(
            Testttt,
            {
                Charge: Title,
                Code1: Level2.Title2,
                Service: Value.Code,
                Code2: Value.Title3
            }
        )
    )
)

2021-03-23-03.png

Hope it could help.

Regards,

Qi

 

Best Regards,
Qi

This works perfectly - and a very simple approach - thank you so much for your help @v-qiaqi-msft 

One last thing though, the results i'm getting contain duplicates from the last table "Service" (This is how the data originally is), any chance we can remove the duplicates?

Thank you again, this has been super helpful

 

Below for reference:

Luka84_0-1616493208632.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,998)