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.

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (2,499)