cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

populate gallery from Excel table with over 10000 registers

Hi all,

 

I hope you can help me. I'm trying to populate a gallery from a Excel table with over 10,000 registers.

With 2,000 registers I don´t have problem.

 

I trying to use a collections with column called "PrimaryKey", but I have a problem 

In OnSelect property Button, I have this:

 

Concurrent(
Collect(CollectionA;Filter(ExcelTable;PrimaryKey<2000));
ClearCollect(CollectionB;Filter(ExcelTable;PrimaryKey>=2000 && PrimaryKey<4000));
ClearCollect(CollectionC;Filter(ExcelTable;PrimaryKey>=4000 && PrimaryKey<6000));
ClearCollect(CollectionD;Filter(ExcelTable;PrimaryKey>=6000 && PrimaryKey<8000))
)

 

When run the app, and check in File -> Collections, I have:

CollectionA with 1999 registers

CollectionB with 1 registers

CollectionC with 0 registers

CollectionD with 0 registers

 

I dont know why starting second collection I dont have any data or what can I do.

 

I change File --> Advanced configuration (500 --> 2000). Save the app and restart the app

 

Thanks in advanced

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: populate gallery from Excel table with over 10000 registers

Hello everyone, if any of you had the same problem, I will tell you the solution:

 

First I was playing with non-delegable functions under a list in sharepoint, what I did was to debug the table in excel before loading to the list including a PrimaryKey autonumber with number form in excel. this has worked for me. then increase the range to 2000 data limit. now even though my list has 7000 if I search or order I can get to all the records.

 

It´s working fine when I create a new register too.

 

Thanks all for your support

View solution in original post

15 REPLIES 15
Highlighted
Community Champion
Community Champion

Re: populate gallery from Excel table with over 10000 registers

Hi @dvalenzuela ,

That is because you have used ClearCollect instead of Collect on all but the first one and need ClearCollect on the first.

Concurrent(
   ClearCollect(CollectionA;Filter(ExcelTable;PrimaryKey<2000));
   Collect(CollectionB;Filter(ExcelTable;PrimaryKey>=2000 && PrimaryKey<4000));
   Collect(CollectionC;Filter(ExcelTable;PrimaryKey>=4000 && PrimaryKey<6000));
   Collect(CollectionD;Filter(ExcelTable;PrimaryKey>=6000 && PrimaryKey<8000))
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Helper I
Helper I

Re: populate gallery from Excel table with over 10000 registers

Thanks for your response:

 

My original code is:

 

Concurrent(
Collect(CollectionA;Filter(ExcelTable;ClavePrimaria<2000));
Collect(CollectionB;Filter(ExcelTable;ClavePrimaria>=2000 && ClavePrimaria<4000));
Collect(CollectionC;Filter(ExcelTable;ClavePrimaria>=4000 && ClavePrimaria<6000));
Collect(CollectionD;Filter(ExcelTable;ClavePrimaria>=6000 && ClavePrimaria<8000))
)

 

and I tried changing the symbols for text: like: && -> And

 

Concurrent(
Collect(CollectionA;Filter(ExcelTable;ClavePrimaria<2000));
Collect(CollectionB;Filter(ExcelTable;ClavePrimaria>=2000 And ClavePrimaria<4000));
Collect(CollectionC;Filter(ExcelTable;ClavePrimaria>=4000 And ClavePrimaria<6000));
Collect(CollectionD;Filter(ExcelTable;ClavePrimaria>=6000 And ClavePrimaria<8000))
)

 

But always have this result:

CountRows(CollectionA) -> 1999

CountRows(Collectionb) -> 1

 

This Excel table is in Sharepoint List and OneDrive, same result for both

 

Thanks,

Highlighted
Community Champion
Community Champion

Re: populate gallery from Excel table with over 10000 registers

Ok @dvalenzuela ,

Actually I missed something in my first post as I answered an almost identical question yesterday - so please disregard this as I was on the wrong path to the problem.

Firstly, get rid of the Concurrent for the moment and you can put back ClearCollect if you are using different collections- you can actually do this is one - firstly do this as Excel I believe returns numbers as text at times.

ClearCollect(
   CollectionA;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)<2000
   )
);
ClearCollect(
   CollectionB;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=2000 && 
      Value(ClavePrimaria)<4000
   )
);
ClearCollect(
   CollectionC;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=4000 && 
      Value(ClavePrimaria)<6000
   )
);
ClearCollect(
   CollectionD;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=6000 && 
      Value(ClavePrimaria)<8000
   )
)

If that works, try this

ClearCollect(
   CollectionXL;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)<2000
   )
);
Collect(
   CollectionXL;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=2000 && 
      Value(ClavePrimaria)<4000
   )
);
Collect(
   CollectionXL;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=4000 && 
      Value(ClavePrimaria)<6000
   )
);
Collect(
   CollectionXL;
   Filter(
      ExcelTable;
      Value(ClavePrimaria)>=6000 && 
      Value(ClavePrimaria)<8000
   )
)

You can certainly put the Concurrent back on the first one - you need to test the second if you need to do this.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Dual Super User
Dual Super User

Re: populate gallery from Excel table with over 10000 registers

You can't work with that many records in an Excel file.  There is an upper limit.  You can Use the Excel connector to import the records into a collection, but you won't be able to write them back.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Community Champion
Community Champion

Re: populate gallery from Excel table with over 10000 registers

Thanks @Pstork1 ,

My next suggestion was actually to look at SharePoint.

Highlighted
Helper I
Helper I

Re: populate gallery from Excel table with over 10000 registers

Hi,

 

I used this:

 

Concurrent(
ClearCollect(
CollectionA;
Filter(
ExcelTable;
Value(ClavePrimaria)<2000
)
);
ClearCollect(
CollectionB;
Filter(
ExcelTable;
Value(ClavePrimaria)>=2000 &&
Value(ClavePrimaria)<4000
)
);
ClearCollect(
CollectionC;
Filter(
ExcelTable;
Value(ClavePrimaria)>=4000 &&
Value(ClavePrimaria)<6000
)
);
ClearCollect(
CollectionD;
Filter(
ExcelTable;
Value(ClavePrimaria)>=6000 &&
Value(ClavePrimaria)<8000
)
))

 

And the result was:

 

"Items ColecctionA: " & CountRows(CollectionA)&"-"&"Items ColecctionB: " & CountRows(CollectionB)&"-"&"Items ColecctionC: " & CountRows(CollectionC)&"-"&"Items ColecctionD: " & CountRows(CollectionD)

 

 

Items ColecctionA: 1999-

Items ColecctionB: 1-

Items ColecctionC: 0-

Items ColecctionD: 0

 

Sorry, I dont know why the second collection always load just one register.

 

Thanks for your help

Highlighted
Helper I
Helper I

Re: populate gallery from Excel table with over 10000 registers

Hi,

 

I have followed your suggestion, I create a new App connecting to a Sharepoint List, this is my code:

Concurrent(
ClearCollect(
CollectionA;
Filter(
'Sharepoint List';
Value(ID)<2000
)
);
ClearCollect(
CollectionB;
Filter(
'Sharepoint List';
Value(ID)>=2000 &&
Value(ID)<4000
)
);
ClearCollect(
CollectionC;
Filter(
'Sharepoint List';
Value(ID)>=4000 &&
Value(ID)<6000
)
);
ClearCollect(
CollectionD;
Filter(
'Sharepoint List';

 

But my problem persists:

Items ColecctionA: 1999-Items ColecctionB: 1-Items ColecctionC: 0-Items ColecctionD: 0
Value(ID)>=6000 &&
Value(ID)<8000
)
))

 

Thanks

Highlighted
Helper I
Helper I

Re: populate gallery from Excel table with over 10000 registers

Hi,

I have also tried removing  function Value() in ID, but the same problem.

I dont knows the second collection only have 1 register

 

Thanks

Highlighted
Helper I
Helper I

Re: populate gallery from Excel table with over 10000 registers

Maybe the problem is with Filter? I have a message with blue line over ID<2000 that says:

Maybe dont work with large size files

 

Thanks

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (8,697)