#PowerPlatformTip 66: 'Load Data Source Structure'

TL;DR: Use ClearCollect(MyCollection, FirstN(MyDataSource, 0)) to load a data source’s empty schema into a collection so later patch operations align perfectly. Filter(MyDataSource, false) does the same. Filter(COLLECTION, Defaults(Source)) is not valid Power Fx.

💡 Challenge

You need to load a data source structure into a collection, especially if you plan to patch the collection back to the data source later on.

✅ Solution

Load zero rows from the data source into the collection. The rows are empty, but the columns and their types come along, and that is what later Patch operations need.

🔧 How It’s Done

1. Load the empty schema

🔸 FirstN(MyDataSource, 0) returns an empty table with every column of the data source. On a Dataverse Accounts table CountRows(FirstN(Accounts, 0)) is 0 and ShowColumns(FirstN(Accounts, 0), 'Account Name') returns <empty table> with the column in place.

🔸 Filter(MyDataSource, false) returns the same empty, typed table.

🔸 Filter(COLLECTION, Defaults(Source)), the formula this tip originally showed, does not compile: the second argument of Filter must be a condition, and a record in that position gives the error Boolean value expected.

2. Populate the collection with the proper structure

🔸 Use ClearCollect(MyCollection, FirstN(MyDataSource, 0)).

🔸 Replace MyDataSource with your actual data source name.

3. What the typed schema protects you from

🔸 Column types are fixed by the first rows a table gets. Table({ID:1}, {ID:"x"}) shows the problem: the first row sets ID to number, and the second row comes back as <error> because "x" does not fit.

🔸 Loading FirstN(MyDataSource, 0) first fixes the column types to those of the data source, so the collection and the data source agree on every column before any row is added.

🎉 Result

You’ve set up a structured collection that matches the data source schema, ensuring smoother data handling and an enhanced app experience.

🌟 Key Advantages

🔸 Matching types: every column of the collection has the type of the data source column before the first row is added.

🔸 Prepare offline, write once: collect and edit rows in memory, then write them to the data source in one call.

🔸 No dummy rows: the collection starts empty, so you don’t have to remove a placeholder row later.

🛠️ FAQ

1. Why not Filter(COLLECTION, Defaults(MyDataSource))?

Because it is not valid: Filter expects a true/false condition as its second argument, and Defaults() returns a record. The formula fails with Boolean value expected. FirstN(MyDataSource, 0) or Filter(MyDataSource, false) give the empty, typed table that was intended.

2. Will this method retrieve any existing records from the data source?

No. FirstN(MyDataSource, 0) returns zero rows (CountRows is 0), only the columns. To load actual data, use Filter(MyDataSource, ...) or Collect(MyCollection, MyDataSource).

3. How do I write the collection back to the data source?

To create every row of the collection as a new record, use Collect(MyDataSource, MyCollection). To change existing records, use the table form of Patch: Patch(MyDataSource, BaseRecordsTable, ChangeRecordsTable), where both tables have the same number of rows in the same order. #PowerPlatformTip 57 shows bulk patch patterns in detail.

4. What goes wrong without the schema?

A value of the wrong type ends up as an error row instead of a value: Table({ID:1}, {ID:"x"}) returns 1 and <error>. Loading the schema first makes the collection’s column types match the data source before any data arrives.

🔗 Related Tips

🔸 #PowerPlatformTip 57: ‘Mastering PATCH’ - the bulk-patch operation this schema-matched collection is designed to feed into.

🔸 How to Load Multiple Data Sources into ONE Collection - combine this schema-loading trick with data from more than one source.