cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dvalenzuela
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
dvalenzuela
Helper I
Helper I

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
WarrenBelz
Super User III
Super User III

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.

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,

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.

Pstork1
Dual Super User III
Dual Super User III

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.

Thanks @Pstork1 ,

My next suggestion was actually to look at SharePoint.

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

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

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (69,693)