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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Sharepoint List Extracted Data

Hello,

 

I extracted data from a Sharepoint list and in some columns I am getting this "<div class=..."  instead of just the data that appears on a Sharepoint list. How can I be able to display in Power BI just the data that appears in the Sharepoint list?

 

Thanks

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

@Anonymous  Instead of changing your SharePoint list column, you can add a custom column in the query editor with this formula to get the text from the HTML code.  Replace "RichText" with your column name.

 

= Html.Table([RichText] , {{"text",":root"}}){0}[text]

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

7 REPLIES 7
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Do the solutions in my reply work in your scenario?

 

 

Best Regards,

Icey

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Please check if these can help you:

 

1. If there are one column named “FieldValuesAsText”, expand the column “Description”.

Reference:

Extract Plain Text From SharePoint Rich Textbox Field In Power BI (c-sharpcorner.com)

Using Power BI to Report on Rich Text Data Fields in SharePoint – The White Pages (unlimitedviz.com)

 

2.  Switch the source field in SharePoint to plain text

Solved: Importing Multiple Lines of Text field from ShareP... - Microsoft Power BI Community

 

3. If you need to retain rich text, try this:


 

Okay - you will need some more steps, but I was able to go from 

this: 

Icey_0-1618542338544.png

 

 

To This: 

Icey_1-1618542338527.png

 

 

Step 1: Replace </p><p> with ; (to add a delimiter between the colors in the same row)

Step 2: Split your column by position 37 to split the div class string from the start of the first color

Step 3: Replace </p></div> with blank

Step 4: Split Coloum by delimiter ;

Step 5: Unpivot the columns containing your text values you want to keep

Step 6: Remove other columns than the column you got from your unpivot

Step 6: Remove dublicates in your remaining column

 

This is my entire M code: (the source is just to create a table with the 3 values you gave me)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBOTsksU0jOSSwuto1Rcq0oSS3KS8xxBvEdnZxdDI2MTcDKUiGKCyAc96LU1DyIiH4BFnmnnNJUPNKRqTk5+eXYFOgDXQPhK8XqEHQhyHUgV5LgAtIscDR0MnI2dsEWBM75OflFxYSsiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"</p><p>",";",Replacer.ReplaceText,{"Text"}),
    #"Split Column by Position" = Table.SplitColumn(#"Replaced Value","Text",Splitter.SplitTextByPositions({0, 37}, false),{"Text.1", "Text.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Split Column by Position","</p></div>","",Replacer.ReplaceText,{"Text.2"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1","Text.2",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"Text.2.1", "Text.2.2", "Text.2.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Text.1"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"
/sdjensen

 

Reference: https://community.powerbi.com/t5/Power-Query/Importing-Multiple-Lines-of-Text-field-from-SharePoint-list/m-p/42840#M4027 

 


 

 

Best Regards,

Icey

 

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

mahoneypat
Microsoft Employee
Microsoft Employee

@Anonymous  Instead of changing your SharePoint list column, you can add a custom column in the query editor with this formula to get the text from the HTML code.  Replace "RichText" with your column name.

 

= Html.Table([RichText] , {{"text",":root"}}){0}[text]

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi, 
I am having the same issue, I tried the above solution, using the formula and it came back with the error: We cannot convert a value of type Record to type Text. 

Can you assist please? @mahoneypat  @Icey 

 

Thanks, 
Jaide 

AlexisOlson
Super User
Super User

You probably have it set to rich text or enhanced rich text instead of plain text in the column settings in SharePoint.

 

AlexisOlson_0-1618323579375.png

 

Anonymous
Not applicable

How can I get to this window?

List Settings and then click the specific column.

 

AlexisOlson_0-1618326634785.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors