Sharepoint List Extracted Data

[Anonymous]

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

[Icey]

Hi @Anonymous ,

Do the solutions in my reply work in your scenario?

Best Regards,

Icey

[Icey]

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: 

To This: 

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"

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 · Accepted solution]

@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

[Jaide17]

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]

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

[Anonymous]

How can I get to this window?

[AlexisOlson]

List Settings and then click the specific column.