cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blueandgold
Frequent Visitor

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 🙂 Can anyone point me in the right direction? I appreciate any help you can provide. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-xida-msft
Community Support
Community Support

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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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