ForAll function to import data from excel to a collection

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I'm trying to take the data from the excel DataSource and place it in a Collection.

I have a Excel Datasource: Table1_2

One field "Supplier_Name"

I have a Collection called "COLLECT_BrandList"

One field "Supplier_Name"

I'm using the function ForAll() Does this seem correct ?

Function:

ForAll(Table1_2, Patch(COLLECT_BrandList, Defaults(COLLECT_BrandList),{Supplier_Name: Supplier_Name}))

Thanks

Dave

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

GarethPrisk 2,828 on at

Like
a
(1)

Report
Copy link

Link copied!
Collect(COLLECT_BrandList,Table1_2)

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@GarethPrisk
just asking, was there anything wrong with the ForAll function ?

I plan on having around 5,000 records in the excel table ?
any issues...I placed 502 records in the Excel table just to see if I would have any delegation issues.

Dave

Was this reply helpful? Yes No
Verified answer

GarethPrisk 2,828 on at

Like
a
(1)

Report
Copy link

Link copied!
The ForAll function is effectively a loop. It will take a table, and loop through each of the rows, and then allow you perform an action against that row.
The Collect function is effectively creating a row in a table.
However, the Collect function does support creating a collection (table) from a source (almost always tabular). I don't know if it's short-handing what the ForAll is doing, but it's only one function to invoke. If you do the ForAll approach, you would be invoking the Collect, or Patch, or whatever function(s) over-and-over again.

In your example
ForAll(Table1_2, Patch(COLLECT_BrandList, Defaults(COLLECT_BrandList),{Supplier_Name: Supplier_Name}))
The ForAll usage is valid, it will apply a function(s) against each row of the Excel table
The Patch function is mostly valid
The Collection would have been invoked in another step
Collections typically don't have Defaults, they're just tables with columns based on the data that is being put into them (meaning you don't define a data type, it just knows)
You are mapping a source column to an identical column in the collection - I would avoid this
Again, this would invoke the Patch function X times, per X rows in Table1_2
As noted in previous post, this can be simplified to invoking a single function Collect(collectionName, tableName)

I think you can have as many columns as you want in the Excel table. Excel as a static data source, to my knowledge, is not subject to any delegation issues. In fact, I just tested an Excel table with 10,000 rows and it was collected instantly, without issue.

Was this reply helpful? Yes No