Forum Discussion
Selecting a FieldValuesAsText record throws a Bad Request error.
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
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.
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:Calibri, Arial, Helvetica, sans-serif;font-size:11pt;color:rgb(0, 0, 0);"><span style="color:black;"><p><span style="color:black;font-family:arial;"><b></b></span><span style="color:black;font-family:arial;"><b></b></span><span style="color:black;font-family:arial;"><b></b></span><span style="font-family:arial;"><b>Bold Heading Text</b></span></p>Sentence 1.<div style="margin-top:14.6667px;margin-bottom:14.6667px;"><span style="font-family:Calibri, Arial, Helvetica, sans-serif;background-color:rgb(255, 255, 255);display:inline !important;"></span><span style="font-family:Calibri, Arial, Helvetica, sans-serif;background-color:rgb(255, 255, 255);display:inline !important;"><span style="font-family:Calibri, Arial, Helvetica, sans-serif;background-color:rgb(255, 255, 255);display:inline !important;">Sentence 2.</span></span><div style="margin-top:14.6667px;margin-bottom:14.6667px;"><span style="font-family:Calibri, Arial, Helvetica, sans-serif;background-color:rgb(255, 255, 255);display:inline !important;"></span><span style="font-family:Calibri, Arial, Helvetica, sans-serif;background-color:rgb(255, 255, 255);display:inline !important;">Sentence 3.</span><span style="font-family:Calibri, Arial, Helvetica, sans-serif;background-color:rgb(255, 255, 255);display:inline !important;"></span><br></div><br></div></span><br></div></div>
to create this is overkill and contains inefficent code.