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.