Forum Discussion

Aedmunds's avatar
Aedmunds
Frequent Visitor
3 years ago

SharePoint Connector 2.0 vs 1.0 ... suddenly missing all Lookup field IDs

So I have about 70 tables ... about 30 are level 1 lookups (i.e. not dependent on anything else), about another 10 level 2 lookups which use level 1 ... e.g. Company (level1) > Division (level2).

 

I then have another 20 or so transaction tables where 300-400 users are constantly creating records in lists ... obviously using the lookups.  I hit a refresh brick wall in Power BI running out of resources with some of those transactions doing expand columns to get the User List Full Name etc..

 

So ... it was suggested I move to Implementation 2.0 instead of Implementation 1.0 for the connector.

 

This is great from a performance standpoint, so much faster, seconds instead of minutes ... BUT ... on deeper investigation, I've hit a problem.

 

When I connect to a SharePoint list with v2.0, the Lookup ID is missing from all lookups ... similarly to above example, I'm expecting to find a CompanyID in my Division table, but it just brings back Company [List]

 

In v1.0 this was natively brought across.  It seems the only way to get the lookupID visible (which is what I use in all my relationships), is to expand the column and expand the field to select the ID!?!?

 

If I have to do this for 70 tables and several hundred fields I'm going to lose the plot, and then have to rebuild the whole datamodel again.  Surely this can't be the final version of v2?  That is a major regression in my eyes.  I just moved all my imports to dataflow to avoid these performance issues.

 

It seems very half-arsed ... or have a missed something completely?

 

Any thoughts appreciated?

 

Need Help

2 Replies

    • Aedmunds's avatar
      Aedmunds
      Frequent Visitor
      3 years ago

      Thanks for the information Pat.  However, I think in this instance for the work I'm doing and the number of tables with more than 5000 rows, REST will create an unnecessary coding headache for minimal gain.  I'm going to simply have to go through each and every one of the 70 tables and expand the lookups. At least it still performs really well ... just did a test on a timesheet table with 9 lookups ... v2.0 ran in 5 seconds, v1.0 took almost 20mins.