cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blueandgold
Level: Powered On

Creating collection from multiple spreadsheet tables

Hi everyone,

 

I would like to create a collection from three separate tables that all have a column of identical values.

 

For a simplified example, I would like to create the collection "MyBuildingInfo" from Table 1 (BuildingName column, BuildingRoof column), Table 2 (BuildingName column, BuildingExterior column), and Table 3 (BuildingName column, BuildingBasement column) -- the final collection being "MyBuilding Info" with BuildingName, BuildingRoof, BuildingExterior, and BuildingBasement being the four columns. If the building name matches, then add rest of that table record's data to the matched collection's record.

 

Do I need to build a blank collection structure first, or should I collect Table 1 and then add columns for the additional info? I tried to collect each table but it didn't match/patch based on the building names like I had assumed it would from some of the examples in the "Collections" article.

 

I have tried "Patch", "ClearCollect", "ForAll" and "AddColumns" in various combinations but I'm clearly just not understanding the process Smiley Happy Can anyone point me in the right direction? I appreciate any help you can provide. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Creating collection from multiple spreadsheet tables

Hi @blueandgold,

Do you want to combine/join three Tables into one Collection?

Is the BuildingName column a Distinct column in all your three Tables?

The user @Mickell2030 has faced same issue with you, please check the response within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/Syntax-for-joining-tables/td-p/61387

I assume that the BuildingName column is a Distinct column in all your three Tables, I think the AddColumns function could achieve your needs. Please take a try with the following workaround:2.JPG

Set the OnVisible property of the first screen of my app to following formula:

ClearCollect(
    Table1,
    {
        BuildingName: "Kris Dai",
        BuildingRoof: "PowerApps"
    },
    {
        BuildingName: "Teresa Liu",
        BuildingRoof: "Microsoft Flow"
    }
);
ClearCollect(
    Table2,
    {
        BuildingName: "Kris Dai",
        BuildingExterior: "Round"
    },
    {
        BuildingName: "Teresa Liu",
        BuildingExterior: "Rectangle"
    }
);
ClearCollect(
    Table3,
    {
        BuildingName: "Kris Dai",
        BuildingBasement: "Concrete"
    },
    {
        BuildingName: "Teresa Liu",
        BuildingBasement: "Earth"
    }
)

Set the OnSelect property of the Button control to following:

ClearCollect(
    MyBuildingInfo,
        AddColumns(
            Table1,
            "BuildingExterior",
            LookUp(Table2, BuildingName = Table1[@BuildingName], BuildingExterior),
            "BuildingBasement",
            LookUp(Table3, BuildingName = Table1[@BuildingName], BuildingBasement)
          )
)

On your side, you should type:

ClearCollect(
    MyBuildingInfo,
        AddColumns(
            YourTable1DataSource,
            "BuildingExterior",
            LookUp(YourTable2DataSource, BuildingName = YourTable1DataSource[@BuildingName], BuildingExterior),
            "BuildingBasement",
            LookUp(YourTable3DataSource, BuildingName = YourTable1DataSource[@BuildingName], BuildingBasement)
          )
)

The MyBuildingInfo Collection created as below:3.JPG

More details about LookUp function in PowerApps, please check the following article:

LookUp function

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Creating collection from multiple spreadsheet tables

Hi @blueandgold,

Do you want to combine/join three Tables into one Collection?

Is the BuildingName column a Distinct column in all your three Tables?

The user @Mickell2030 has faced same issue with you, please check the response within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/Syntax-for-joining-tables/td-p/61387

I assume that the BuildingName column is a Distinct column in all your three Tables, I think the AddColumns function could achieve your needs. Please take a try with the following workaround:2.JPG

Set the OnVisible property of the first screen of my app to following formula:

ClearCollect(
    Table1,
    {
        BuildingName: "Kris Dai",
        BuildingRoof: "PowerApps"
    },
    {
        BuildingName: "Teresa Liu",
        BuildingRoof: "Microsoft Flow"
    }
);
ClearCollect(
    Table2,
    {
        BuildingName: "Kris Dai",
        BuildingExterior: "Round"
    },
    {
        BuildingName: "Teresa Liu",
        BuildingExterior: "Rectangle"
    }
);
ClearCollect(
    Table3,
    {
        BuildingName: "Kris Dai",
        BuildingBasement: "Concrete"
    },
    {
        BuildingName: "Teresa Liu",
        BuildingBasement: "Earth"
    }
)

Set the OnSelect property of the Button control to following:

ClearCollect(
    MyBuildingInfo,
        AddColumns(
            Table1,
            "BuildingExterior",
            LookUp(Table2, BuildingName = Table1[@BuildingName], BuildingExterior),
            "BuildingBasement",
            LookUp(Table3, BuildingName = Table1[@BuildingName], BuildingBasement)
          )
)

On your side, you should type:

ClearCollect(
    MyBuildingInfo,
        AddColumns(
            YourTable1DataSource,
            "BuildingExterior",
            LookUp(YourTable2DataSource, BuildingName = YourTable1DataSource[@BuildingName], BuildingExterior),
            "BuildingBasement",
            LookUp(YourTable3DataSource, BuildingName = YourTable1DataSource[@BuildingName], BuildingBasement)
          )
)

The MyBuildingInfo Collection created as below:3.JPG

More details about LookUp function in PowerApps, please check the following article:

LookUp function

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

Top Community Contributors for September  2019

Top Community Contributors for September 2019 🎉🎉

Let's thank our top community contributors

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 272 members 4,183 guests
Please welcome our newest community members: