cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luka84
Advocate III
Advocate III

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

View solution in original post

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,960)