Columns vary based on how data is retrieved.

[Anonymous]

I have a large SharePoint list (approx. 18,000 rows). It takes a significant amount of time to refresh so I've been researching alternatives, like using REST API. When I retrieve the data using the SharePoint list or SharePoint Online list connector, all of the data is available; lookup columns can be expanded, etc... I stumbled upon a guide for connecting to the REST API to retrieve the same data but I've enountered an issue where not all of the columns appear. 

When I run the query using the SharePoint Online list connector it looks like this: 

let
    Source = SharePoint.Tables("my SharePoint URL", [ApiVersion = 15]),
    #"MyListID" = Source{[Id="XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"]}[Items]
in
    #"MyListID"

All of the data is available and the lookup columns can be expanded and viewed. The only issue I have with this is the time it takes to refresh the data.

When I run the query using REST API, I used the template found on Hooser BI's YouTube video here.

let
    sitename ="<my site>", // if a subsite use "Site/SubSite"
    listid = "MyListID",
    baseurl = "https://<my SharePoint URL>/sites/" & sitename & "/_api/web/lists/GetByID('" & listname & "')/",
    itemcount = Json.Document(Web.Contents(baseurl&"ItemCount", [Headers=[Accept="application/json"]]))[value],
    skiplist = List.Numbers(0, Number.RoundUp(itemcount/5000), 5000),
    #"Converted to Table" = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Skip"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Skip", type text}}),
    //fieldselect = "&$top=5000", // all fields with no expansion
    //fieldselect = "&$top=5000&$select = Id,Title,Person,Date", // list desired fields (no expansion)
    fieldselect = "&$top=5000&$select=*",
    Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl& "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, [Headers=[Accept="application/json"]]))),
    #"Expanded Items" = Table.ExpandRecordColumn(Custom1, "Items", {"value"}, {"value"}),
    #"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value")
in
    #"Expanded value"

The MAJORITY of the data comes through, but regardless of what I tweak (I've tried all sorts of combinations of expanding columns) I cannot get the same lookup columns to appear - the data just stops after the Duplicate Record column.  

Is there a fundamental difference in the way data is displayed between these two queries? 

I hope I'm missing something simple here. I'm happy to clarify or expand if the need arises.

Thanks.

[Anonymous]

Hi @Anonymous ,

Hi @Anonymous ,

Not very clear, you may refer to

SharePoint 2013 REST-API: Reliable way to retrieve all field values of a list item? - SharePoint Stack Exchange

Lists and list items REST API reference | Microsoft Docs

Best Regards,
Eyelyn Qin

[Anonymous]

When I use the built-in SharePoint online connector in Power BI to retrieve my data, I receive 102 columns. When I attempt to get the data using REST API (which is much faster), I receive 87 columns. 

I've reviewed the link you sent prior to submitting my question as it did not help me. Maybe I'm misunderstanding how to ask this question, but there is clearly a difference in how the data is displayed when I use API versus the built-in SharePoint connector. 

Any time I place an API call to my SharePoint list and end the call with items 

https://mysharepoint/sites/mysite/_api/web/lists/GetByID('mylistid')/items

I only receive 87 columns.

Any time I place a call and end with items(and the item number in the parenthesis)

https://mysharepoint/sites/mysite/_api/web/lists/GetByID('mylistid')/items(1)

I receive 102 columns.

I'm asking why these would be different. I've tried virtually every combination of field expansion I can think of. I looked into what the actual lookup column names are and tried to use those to expand, but again, whenever I end with /items and do not include the specific item number, the expand field request fails and I don't get any data.

[Anonymous · Accepted solution]

I still don't understand why the two queries yield different results but I have resolved my issue based on a StackOverflow thread I came across: sharepoint - Retrieve User Properties from a lookup Column using REST api - Stack Overflow

Thanks.

[Anonymous]

It works when I grab the item individually and retrieve using FieldValuesAsText. All columns appear. I guess I need to work out a way to run through each item in my giant list and retrieve the item's contents individually?

Source = Json.Document(Web.Contents("https://mysharepoint/sites/mysite/_api/web/lists/GetByID('mylistid')/items(186)/FieldValuesAsText", [Headers=[Accept="application/json"]]))

[Anonymous]

Ignore the typo in the REST API code. The third line should read:

baseurl = "https://<my SharePoint URL>/sites/" & sitename & "/_api/web/lists/GetByID('" & listid & "')/",

 Listname was another variation I was testing.