Forum Discussion

hjaf's avatar
hjaf
Advocate I
6 years ago
Solved

SharePoint list query alternative or optimization

Hello everyone!   Because sharepoint list queries quickly comes too slow to work with in Power BI, I have experimented with the query that sharepoint generates for excel. This is vastly more effect...
Need Help
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    hjaf FYI that I finally made a video to describe this approach, and am adding it here for others that may find this post.  It also gets the count of items and makes the right number of API calls.

    Get SharePoint List Data with Power BI ... Fast - YouTube

     

    Also, a reminder to mark one of these as the solution.

     

    Regards,

    Pat

ElliotK's avatar
ElliotK
Helper I
4 years ago

I really like this method, it is super fast and super easy to configure. However, I have found that whilst using this method, it doesn't pick up all the fields. I have tried expanding all the rows and columns, selecting all values but if simple does not pick up the one field I need. I really don't want to use a v2 connector as it is slow and terrible. 

 

Any help would be appreciated. 

CmdrKeene's avatar
CmdrKeene
Helper IV
4 years ago

I can try to poke around and see if I can find a solution. What type of field is missing? 

 

I will mention that person fields did not come in in a format that was great for me, so in my power query I also pulled in the user information table and then used the user ID to relate to it.

 

 

  • ElliotK's avatar
    ElliotK
    Helper I
    4 years ago

    The field in questrion is called Name, which appears to be a text field. This field is required as it links back to a SharePoint attachment so I wish to use the values contained to connect to the corresponding attachment via its URL. 

     

    The field in question IS exposed if I use a v2 connector, but I did not want to go down this route and would much rather use your solution. 

    • CmdrKeene's avatar
      CmdrKeene
      Helper IV
      4 years ago

      That's really odd, for me all the text fields just show up.  Is it maybe a lookup field on the front-end of SharePoint?  Or you're sure it's either "single line of text" or "multiple lines of text" on the list itself?

      • ElliotK's avatar
        ElliotK
        Helper I
        4 years ago

        Hello CmdrKeene,

         

        I hope you are well?

         

        Thank you for looking into this. I have spoken to the developer and have been advised that the name is indeed a lookup. Apparently it does not appear on the underlying dataset, which I understand. It's a shame as the v2, whilst extremely slow, does pick up the field. Maybe because it is interogating the UL rather than the data itself?

         

        Is there a way to GetByForm or GetByURL?