Creating collection from multiple spreadsheet tables

[blueandgold]

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!

[v-xida-msft · Accepted solution]

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:

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:

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

LookUp function

Best regards,

Kris