Data Flow Error: Your mashup exceeded the maximum length allowed by this service and cannot be saved

[HeihoSilver]

Hi,

Currently I have 100+ Sharepoints that needs to be compiled into 1 table. The number of Sharepoints will grow gradually. 

Add Sharepoint connection, applied steps and compile in Desktop is extremely slow when I have around 70+ Sharepoints connected already. I need to wait for several hours and crash.

It also effects other application I'm using, it becomes very slow and less responsive. I have 16 gb ram. 

So as alternative some suggested to use Data Flow. I have successfully added 30 Sharepoints into Data Flow and compile into 1 table which I used as a single data source in the Desktop. Everything is working well until I add more Sharepoints in Data Flow. 

Error message appear as illustrated in the picture below:

Do you know what could be the issue and how to solve this?

Any other idea or work around on how to handle more than 100+ or much more Sharepoints and compile into 1 table?

Best regards,

HeihoSilver

[HeihoSilver]

@edhans 

I mean I have 100+ Sharepoint lists from different Sharepoints (e.g. Sharepoin Project A, Sharepoint Project B, etc.). Each list is standardized so each columns contain the  same data category and type. 

What I do is compiling all of these Sharepoint Project ... lists in PowerBI desktop using Append method into 1 common table Experience Sharing Table (see the picture below where I compile 80 different project Sharepoint..).

It starts to getting slow when I add around 20 Sharepoint list, it's getting worse and always crash after waiting for hours when adding more than 80 lists. I Also have unchecked "Enable load" to reduce the load and only the common table which is loaded to the Dekstop. 

So I use Data Flow instead as suggested by other. The method is the same by appending all the lists into 1 common table,  but turn out with error message when connecting/adding Sharepoint list no.30. There will be more than 100+ Sharepoint lists that need to be compiled, and the number will grow gradually).

Is there any work around to handle this?

Or how to solve the error issue above?

Best regards,

[v-rongtiep-msft]

Hi @HeihoSilver ,

Please check if you are copying data from a large table in Excel and pasting this in to Power BI, to then manipulate the data in Power BI. The error appeared when attempting to paste the data in.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

There may be a limit on the Table.Combine function, which is the Append operation in Power Query.

What if you put 20 list in "Combination 1", 20 in "Combination 2" etc, then you may have a few dozen Combination X queries. Then combine those.

[edhans]

What do you mean "SharePoints?" SharePoint is a storage location, and can store files and lists. You don't combine "SharePoints." You combine files, or you can combine (Merge or Append) lists. 

Can you be more specific on what you are doing?

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.