cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DAVIDPOWELL
Level: Powered On

ForAll function to import data from excel to a collection

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
GarethPrisk
Level: Powered On

Re: ForAll function to import data from excel to a collection

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.

View solution in original post

3 REPLIES 3
GarethPrisk
Level: Powered On

Re: ForAll function to import data from excel to a collection

Collect(COLLECT_BrandList,Table1_2)
DAVIDPOWELL
Level: Powered On

Re: ForAll function to import data from excel to a collection

@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

GarethPrisk
Level: Powered On

Re: ForAll function to import data from excel to a collection

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.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,316)