OData FieldValuesAsText -- only want to return one of the properties

[Anonymous]

Team,

Because of slow retrieval times, we are trying to limit the return payload on the ODATA / API GET call to Sharepoint online. So we want to expand the field FieldValuesAsText, but only want to return one child field, for example TITLE.

This code fragment looks like it should work (it works on other complex fields) and only bring back TITLE, but it still appears to still bring everything under FieldValuesAsText?  

"..../sites/financeproject/_api/web/lists/getbytitle('Test')/items?$select=Title,FieldValuesAsText/Title"

Are we missing something?

Jim

[Anonymous]

Adding more info -- it appears that the FieldValuesAsText returns a JSON string of all of the field values in a LIST, so I guess it becomes am extraction exercise? Any Sharepoint API / Odata experts out there?

[v-xuding-msft]

Hi @Anonymous ,

You could try to change the list to table firstly and then expend the column. 

I test in Desktop, it can choose the columns while expending FieldValuesAsText column.  I can't reproduce your scenario. If the way can't help you, can you please share a screenshot for your scenario?  Can you please share more details to us?

• Where do you transform the data? Query Editor in Power BI Desktop or Service?
• You connect SharePoint Online via OData. Right?

Best Regards,

Xue Ding

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

[Anonymous]

Hi Xue,

To clarify, I can expand the field FIELDVALUESASTEXT just fine in Power BI desktop, so we can pull the data OK. The issue we are having is that it can take up to 45 minutes to pull back only 5,000 records from a Sharepoint Online LIST.

So we switched over to a ODATA.FEED connection as opposed to the default Sharepoint LIST connector. Our hope was that we can choose only the fields we want to cut down on the amount of uneccessary data returned. I would like to expand on the server using a combation of $select and $expand. This approach works with other complex fields, but not with FIELDVALUESASTEXT -- it appears to pull all of the child properties as one large JSON string regardless of any ODATA syntax such as $select =PARENT/CHILD&$expand=PARENT.

Any insight is appreciated, as we are researching as well.

Jim

[trebgatte]

Please see my blog post where I demonstrate how to expand FieldValuesAsText and only retrieve two fields. Maybe this will help.

https://marqueeinsights.com/how-to-data-mine-a-sharepoint-list-with-power-bi/

Thanks!

--Treb

Check out my Power BI blog posts at https://marqueeinsights.com/blog

[Anonymous]

Hi Treb,

Thanks for the video. We are able to pull data in OK using FIELDVALUESASTEXT -- it's just not performant at all. It can take 45 minutes to pull a few thousand records (and frequently fails). So I was wondering if anyone has experience making ODATA.FEED calls to SP lists > 5000 records with some complex objects included.

Thanks,

Jim

[trebgatte]

What sort of complex objects? I'd like to attempt to replicate the issue.

Also, what version of the PBI Desktop are you using?

Thanks!

--Treb

[Anonymous · Accepted solution]

Hi Treb,

These complex fields were managed metadata from a TERM STORE.  I found a workaround by downloading the list TAXONOMYHIDDENLIST which has all of the text desciptions for each managed entity, and merging that table back to the base list to get all of the text descriptions.

This had the same effect as using FIELDVALUESASTEXT + EXPAND. However, expanding FIELDVALUESASTEXT is a huge performance drain when dealing with lists >5000 records. The TAXONOMYHIDDENLIST approach took the query run time from 60 minutes to 12 minutes.

Thanks,

Jim

[trebgatte]

Also, the SharePoint Online List connector uses the OData url under the covers so no need for you to deal with the complexity.