Answered

Using Sharepoint as data source not working for big data

(0) Share

Report

Posted on by Tanoh

Good Day, I'm just wondering why I cant connect my power apps to sharepoint. I have an excel file contains 73k rows and the size is 4MB and I put it in the sharepoint to connect it to power apps. However it doesn't work. Please advise if do we have an alternative that can handle big data to connect it to Power Apps.

Categories:

Building Power Apps

All responses (11)

Answers (1)

Sort by

Verified answer

JayMagness 1,663 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi,

it sounds like you are attaching the excel to a SharePoint Item and you should import the Excel into SharePoint

Excel makes this very easy you can import structure and date into Sharepoint ... In the Excel workbook --> Table Design -->Export -->to sharepoint, etc.....
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Put the file in OneDrive and connect from the OneDrive. Ensure you format the excel Data as a Table

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.
Tanoh 10 on at

Like
a
(0)

Report
Copy link

Link copied!

We have tried limiting the rows to 100 and it works. However, the original file contains 73k rows the error said "The tables didn't load correct. Please try again.
Tanoh 10 on at

Like
a
(0)

Report
Copy link

Link copied!

We have tried limiting the rows to 100 and it works. However, the original file contains 73k rows the error said "The tables didn't load correct. Please try again."
JayMagness 1,663 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

There are some limitations on data in PowerApps but there are ways around it, may be best to do on teams. I sent you my contact, send me a note there and we can try to do it on teams then post the outcome here
Tanoh 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Our data is confidential. Kindly advise what are the possible ways to connect big data sources.
JayMagness 1,663 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

I do not need to see your data, I am doing this to help as a free service and I just want to resolve this faster...

Check and see if you go to setting and go to 2000 in advanced setting if this brings in more records.

I recommend importing the records into SharePoint then set a field called "Limiter" then for every 1800 records put a letter or a number. First 1,800 pit an "A" , then second 1,800 put a "B", etc.. once you are at "Z" then start with "AA" then "BA", then "CA, etc.... then use clear collect to bring them all into Memory...

I do this a lot for may customers and Users...

Again I would rather help you thorough teams to save time.
timl 37,382 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

@Tanoh

Just to reiterate what @JayMagness says, it is not possible to connect an app to an Excel data source that exceeds 2,000 records, or 2MB.

Therefore, you cannot use your existing Excel spreadsheet as a data source. The solution is to import your data into a SharePoint list, and to use that as the data source for your app.
Tanoh 10 on at

Like
a
(0)

Report
Copy link

Link copied!

@timl I have tried it via MS List. However, it appears that I have exceeded in the number of rows. Please advise if MS List is same as SharePoint List.
JayMagness 1,663 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi all,

I have been round and round with this and this is all a balancing act... Like many things in life with ease comes limitations, and this is no different.

Contrary to popular belief Excel is not a database system, the best chance you have is move to SharePoint, and use Clear Collect to pull in your data. I have a huge install that has over 100,000 records in SharePoint and I solved it all with the process I described earlier.

Also if it gets to a lot of ClearCollect Segments you can add Concurrent to the front but watch the code so things do not run over eachother. Collect, Clear, and ClearCollect functions - Power Apps | Microsoft Docs