Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
pfurbish
Frequent Visitor

Selecting a FieldValuesAsText record throws a Bad Request error.

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.

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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

View solution in original post

4 REPLIES 4
pfurbish
Frequent Visitor

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:

DarylLynchBzy_0-1646311802896.png

 

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

DarylLynchBzy_1-1646311916990.png

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

DarylLynchBzy_2-1646312061806.png

 

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.
DarylLynchBzy_4-1646312190496.png

 

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

DarylLynchBzy_0-1646312389190.png

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.

DarylLynchBzy_1-1646312720981.png

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.

DarylLynchBzy_2-1646312930839.png

 

 

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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

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. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors