Selecting a FieldValuesAsText record throws a Bad Request error.

[pfurbish]

In Power BI desktop (Version: 2.102.845.0 64-bit February 2022), I have 3 simple SharePoint lists (which I recently created) that are queried to create 3 tables.  Two of them work as expect.  For the third one, when I click on the FieldValuesAsText record, the following error is generated:

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (400) Bad Request. (Instance is read-only.)
Details:
DataSourceKind=SharePoint
DataSourcePath=https://XXXXX
SPRequestGuid=XXXXX
Url=https://XXXXX/sites/XXXXX/_api/Web/Lists(guid'XXXXX')/Items(1)/FieldValuesAsText

I worked around this issue once by recreating the SharePoint list but now it is happening again.  Initially, FieldValuesAsText returns expected results but after a few days it starts failing.  I'm pulling the FieldValuesAsText to get the text only values for some rich text fields.  Also, FieldValuesAsHTML throws the same error but FieldValuesForEdit returns expected results but it contains HTML so I cannot use it.

Thanks for any help.

[pfurbish]

My source = SharePoint.Tables(pSharePointLists, [Implementation=null, ApiVersion=15]) so version 1.0.  (pSharePointLists is a parameter with the URL).  It's odd that the issure only happens for 1 out of 3 lists and only after a period of time.

Using FieldValuesAsText for getting the text from rich text fields is all over the blogs so I'm not sure how Microsoft will get away with killing it in v2.0. 

Thanks for the feedback.

[Daryl-Lynch-Bzy]

Hi @pfurbish, it is still possible to get the Rich Text from the new version of the SharePoint.Table connector by using the Html.Table function in Power Query.  This approach would create the same output.   Unfortunately, the output is not great, but this is Sharepoint issue rather that Power Query/Power BI problem.

Please consider the following example where I created a basic list with different columns:

Then I created a Query for each of the SharePoint.Table options:

ApiVersion 14 does not have access to the FieldValuesAsText, but it does contain the "RichText".

ApiVersion 15 does have access to FieldValuesAsText, but the extracted result is not helpful:
This is the text extracted "​​​Bold Heading TextSentence 1.​Sentence 2.Sentence 3."  It is missing line feeds.

Implementation 2 works without FieldValuesAsText, but this can be added by using the Html.Table function.

Same result, same one of steps.  Here is the M script.

let
Source = SharePoint.Tables(URL, [Implementation="2.0"]),
#"Sharepoint List" = Source{[Title="Example List"]}[Items],
#"Removed Other Columns" = Table.SelectColumns(#"Sharepoint List",{"ID", "Title", "Single Text", "Multiple Line Text", "Rich Text"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Extracted Rich Text", each Html.Table( [Rich Text] , {{"text", "span" }}){0}[text] )
in
#"Added Custom"

This is the possibility of extract the individual lines, but SharePoint is creating a helpful Html string.  There loads of efficiencies in the HTML code, it could be cleaner.   But this is a SharePoint problem not Power Query.

All this:

<div class="ExternalClass4725B83F297E4B54B3185FD382D0E118"><div style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;font-size&#58;11pt;color&#58;rgb(0, 0, 0);"><span style="color&#58;black;"><p><span style="color&#58;black;font-family&#58;arial;"><b>​</b></span><span style="color&#58;black;font-family&#58;arial;"><b>​</b></span><span style="color&#58;black;font-family&#58;arial;"><b>​</b></span><span style="font-family&#58;arial;"><b>Bold Heading Text</b></span></p>Sentence 1.<div style="margin-top&#58;14.6667px;margin-bottom&#58;14.6667px;"><span style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;background-color&#58;rgb(255, 255, 255);display&#58;inline !important;">​</span><span style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;background-color&#58;rgb(255, 255, 255);display&#58;inline !important;"><span style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;background-color&#58;rgb(255, 255, 255);display&#58;inline !important;">Sentence 2.</span></span><div style="margin-top&#58;14.6667px;margin-bottom&#58;14.6667px;"><span style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;background-color&#58;rgb(255, 255, 255);display&#58;inline !important;"></span><span style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;background-color&#58;rgb(255, 255, 255);display&#58;inline !important;">Sentence 3.</span><span style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;background-color&#58;rgb(255, 255, 255);display&#58;inline !important;"></span><br></div><br></div></span><br></div></div>

to create this is overkill and contains inefficent code.

[Daryl-Lynch-Bzy · Accepted solution]

Hi @pfurbish - can you please check the Advanced Editor screen to see if you are using the either of the following:

SharePoint.Tables( URL, [Implemention=""])
SharePoint.Tables( URL, [Implemention="2.0"])

You will find the Implementation="2.0" work faster, and it not longer provide access to the FieldValuesAsText and FieldValuesAsHTML. 

This suggests that in the long-term Microsoft does not want people using these nested tables.

Many thanks

Daryl

[pfurbish]

I tried your Html.Table function solution and it's working.  Your detailed example was very helpful.  I expect I may need to adjust the CSS selectors as the data gets more complicated but this is a good start and needed for when FieldValuesAsText goes away.

Thanks for your time and effort on this.