Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Michael Dombrowa]

Does anyone have hints how to improve the performance of loading SharePoint Online Lists. Using a small list (1700 entries) where we remove unwanted columns and expand the “Property” column (to 5 columns) leaving a total of 13 columns. When applying the query, builds up to 40 MB but only produces a 217 KB pbix file. This process also takes over 10 minutes to process 1700 records. I need to process several lists, and some may have more than 500k entries! Did a test on a file with 100k records, applying the query changes build over 4 GB and ran for 8 hours before giving up and cancelling. If one attempts to cancel the query, it seems to take just as long to cancel as the build.

Include the ‘simple’ M Code – no DAX, no calculate measures or column, to be used as a table.

let
Source = SharePoint.Tables(“https://sharepint list/”, [ApiVersion = 15]),
#”1234567890-abcd” = Source{[Id=”1234567890-abcd”]}[Items],
#”Renamed Columns” = Table.RenameColumns(#”1234567890-abcd”,{{“ID”, “ID.1”}}),
#”Removed Columns” = Table.RemoveColumns(#”Renamed Columns”,{“FileSystemObjectType”, “Id”, “ServerRedirectedEmbedUri”, “ServerRedirectedEmbedUrl”, “ContentTypeId”, “ComplianceAssetId”, “PreservationVersion”, “PreservationOriginalListId”, “PreservationOriginalMetadata”, “OData__CopySource”, “CheckoutUserId”, “OData__UIVersionString”, “GUID”, “FirstUniqueAncestorSecurableObject”, “RoleAssignments”, “AttachmentFiles”, “ContentType”, “GetDlpPolicyTip”, “FieldValuesAsHtml”, “FieldValuesAsText”, “FieldValuesForEdit”, “File”, “Folder”, “LikedByInformation”, “ParentList”,”Versions”, “Author”, “Editor”, “CheckoutUser”}),
#”Expanded Properties” = Table.ExpandRecordColumn(#”Removed Columns”, “Properties”, {“Legacy_Workgroup”, “Legacy_RecordNumber”, “Hold”, “HPTRIMID”, “All Holds”}, {“Properties.Legacy_Workgroup”, “Properties.Legacy_RecordNumber”, “Properties.Hold”, “Properties.HPTRIMID”, “Properties.All Holds”})
in
#”Expanded Properties”

——————————
Michael Dombrowa
——————————

[Ian Stuart]

Hi

From my limited experience I have seen two issues:

1.  SharePoint lists. – Quite flaky and unreliable with some limitations.  Is it possible to get ?a database as the source or does the data only exist in SharePoint?  Perhaps get an automated extract working into SQL Server and then go from there.
2.  I have found M to be slow when processing Excel data (decompress then recompress).  Maybe something similar is going on with SharePoint.  My conclusion is that M is suitable for lightweight work only and a heavier duty tool should be use for big ETL work.  That said your query does not seem “heavy” at all!

Removing columns before rename may speed things up a tad.

The expansion may be the main issue.  How long does it take without this step?

Ian

——————————
Ian Stuart
Principal
Altis Consulting
London
+44 7946 535454
——————————
——————————————-

[Michael Dombrowa]

Hi Ian,
Unfortunately the data is not in another database, but is added via a migration process. Decided to let the process run end-to-end with 123K records, and it took 15 hours. The progress pane reflected over 7 GB of data, however the final PBIX was only 14 MB. Modified the model and deleted the columns containing records, i.e. no expansion and the process ended in 10 minutes – vast improvement, but not great for only 123K records (PBIX down to 11.2 MB)

——————————
Michael Dombrowa
——————————
——————————————-

[Kimberly Forshey]

Hi Michael,

I’ve been having major issues with reporting off of SP Online lists as well… this was an answer that I received back from Microsoft:

Performance issue for SharePoint source  typically occurs whenever you have of 3 types of fields being retrieved from the SharePoint list and is trying to expand the generated records for those columns. The field types are:?

 

·     Lookup field?

·     Person/Group?

·     Taxonomy

 

If this is the case,  you can avoid this behavior by way of a classic table join. By doing this, it means that there will be only one call to retrieve the second table, and the rest of the expansion can occur in memory using the common key between the two tables. The performance difference can be massive in some cases.? Here is an example of the workaround :

 

First, we start with the original table, noting the column we want to expand, and ensuring we have the ID of the item so that we can match it. Typically the foreign key is named like the display name of the column with ‘Id’ appended. ?

 

 

let

    Source = SharePoint.Tables(“https://bbbbbbbb.sharepoint.com/”, [ApiVersion = 15]),

    #”22b888a2-f035-4557-a82b-a717e7b16bb6″ = Source{[Id=”22b888a2-f035-4557-a82b-a717e7b16bb6″]}[Items],

    #”Renamed Columns” = Table.RenameColumns(#”22b888a2-f035-4557-a82b-a717e7b16bb6″,{{“ID”, “ID.1”}})

    //,#”Expanded TesLookup2″ = Table.ExpandRecordColumn(#”Renamed Columns” , “TesLookup2”, {“Title”}, {“TesLookup2.Title”})

in

    #”Renamed Columns”

    //#”Expanded TesLookup2″

 

 

Second, we load the secondary table, making sure to include the ‘Id’ which is the foreign key. Right-click on the Queries panel to create a new query ?

 

let

    Source = SharePoint.Tables(“https:// bbbbbbbb.sharepoint.com /”, [ApiVersion = 15]),

    #”6a2cc692-ec9a-42c9-80b5-225c6fc52a79″ = Source{[Id=”6a2cc692-ec9a-42c9-80b5-225c6fc52a79″]}[Items],

    #”Renamed Columns” = Table.RenameColumns(#”6a2cc692-ec9a-42c9-80b5-225c6fc52a79″,{{“ID”, “ID.1”}}),

    #”Removed Columns” = Table.RemoveColumns(#”Renamed Columns”,{“FileSystemObjectType”, “ServerRedirectedEmbedUri”, “ServerRedirectedEmbedUrl”, “ContentTypeId”, “ComplianceAssetId”, “OData__ip_UnifiedCompliancePolicyProperties”, “ID.1”, “Created”, “AuthorId”, “Modified”, “EditorId”, “OData__CopySource”, “CheckoutUserId”, “OData__UIVersionString”, “GUID”, “FirstUniqueAncestorSecurableObject”, “RoleAssignments”, “AttachmentFiles”, “ContentType”, “GetDlpPolicyTip”, “FieldValuesAsHtml”, “FieldValuesAsText”, “FieldValuesForEdit”, “File”, “Folder”, “LikedByInformation”, “ParentList”, “Properties”, “Versions”, “Author”, “Editor”, “CheckoutUser”})

in

    #”Removed Columns”

 

 

 

Finally, we join the two tables using the respective column names that match. You can typically find this field by first expanding the column, then looking for the matching columns in the preview.?

 

 

let

    Source = Table.NestedJoin(ODataPBITest, {“TesLookup2Id”}, Documents, {“Id”}, “Documents”, JoinKind.LeftOuter)

    ,#”Expanded TesLookup2″ = Table.ExpandTableColumn(Source,”Documents”,{“Title”},{“Documents.Title”})

in

    #”Expanded TesLookup2″

Hope that helps!

——————————
Kimberly Forshey
Business Intelligence Analyst
406-203-9876
——————————
——————————————-

[Brett van Gelder]

, this looks like the possible answer to the problem. But I don’t quite understand. What are the two queries you are joining? I guess the first is the list that is loading very slowly, but what is the second one?
?

——————————
Brett van Gelder
Director
——————————
——————————————-

[Kimberly Forshey]

Hi,
The second table is a reference to the same query, keeping only the key and record that you want to expand.
After testing, the method that was given really didn’t improve my query enough. Expanding the people fields was just killing it.
In the end, I ended up just expanding the “Field as Text” column once and using them for all of my people picker fields.

Hope that helps!

——————————
Kimberly Forshey

——————————
——————————————-

[Lutz Bendlin]

Concur with Kimberly’s recommendations. Also: Keep these darn list columns under control! cut them down as much as possible. Anything beyond a dozen columns will quickly become unmanageable.

There have been many other attempts, like using ODATA, or the list API calls directly, or swamping the list with indexes. All eventually futile.

——————————
Lutz
——————————
——————————————-