Sharepoint List Extracted Data

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"

/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.

Hi Anonymous ,

Do the solutions in my reply work in your scenario?

Best Regards,

Icey

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