cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mzeppieri
Regular Visitor

Importing Multiple Lines of Text field from SharePoint list

I have a process where my project teams fill out an InfoPath web form that then publishes their responses on the form into a SharePoint list.  The list is a mix of numerical fields, single line text fields and multiple lines of text fields.

 

 I was able to connect my SharePoint list to PowerBI desktop with no issues.  The numerical and single line text fields translate over into PowerBI no problem.

 

The multiple lines of text fields are not translating over.  Instead they show as follows;

 

<div class = "ExternalClass1234ABCD"><p>Actual Text</p></div>

 

How do I get PowerBI to show the actual text without showing the <div> and </div>

1 ACCEPTED SOLUTION

There are two solutions to this issue.

 

I was able to switch the source field in SharePoint to plain text, and in doing so, stopped the additional text from rendering in PowerBI.  This is a quick fix solution that solved my immediate need.

 

However, the other recommended solution posted above is a more robust approach if you need to retain rich text.  I can see a scenario where I may want to retain text bullets, so I will experiment with the other approach as a long term fix to evolving my dashboard.

 

Thank you!!!

View solution in original post

9 REPLIES 9
sdjensen
Solution Sage
Solution Sage

If the value you always need to remove is the same for each row then you can use a replace function in the query editor.

 

Mark the column with the text and on the transform tab select "Replace Values" in the Value To Find box you put <div class = "ExternalClass1234ABCD"><p> and leave the Replace With box empty. After this you do it again with the value </p></div>

/sdjensen

Thank you for responding.  I am not sure that your recommendation will work as proposed, but maybe a variation will.

 

The nomenclature is the same for each entry, but the specific alphanumerics are different.  So for example PowerBI might currently show:

 

<div class="ExternalClassABCD1234><p>Green</p><p>Blue</p><p>Yellow</p></div>

<div class="ExternalClass1234ABCD><p>Blue</p></div>

<div class="ExternalClassA1B2C3D4><p>Colors</p></div>

 

I tried what you suggested by adding seperate Transform commands to remove the <p>, </p> and </div>.  Now the data shows as:

 

<div class="ExternalClassABCD1234">GreenBlueYellow

<div class="ExternalClass1234ABCD">Blue

<div class="ExternalClassA1B2C3D4">Colors

 

How do I write a Transform command that takes into account that the text between the " " after div class is variable?

 

Also, for the GreenBlueYellow scenario, I need to Transform the text so that anything between <p> </p> shows as its own distinct line of text, almost like a bulletized list.  How would I do that?

 

Your approach seems to remove the garbage that came over.  How do I get PowerBI to recognize that what comes over is a formatted block of text?

 

 

 

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

this: Source.png

 

To This: Result.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

Wow, thank you for creating a desk instruction to implement your solution.  I will give it a go and let you know how it turns out.

Sdjensen's approach I think is the correct one. I just wanted to make sure it was clear why you were getting the div tags and other HTML elements in your data: The field that you're pulling from in SharePoint is set as Rich Text.

 

The original poster mentioned that the other fields came across fine, but the multiple lines of text field did not. It's not because it's multiple lines of text, I suspect, but because the SharePoint column definition is set to allow for Rich Text.

 

It may be completely out of your control, but if the original form/SharePoint site is something in your control, and if there's not a business need to keep the field as rich text, you might simplify things by modifying the original field.

I will try this as well.  I can control the text setting, and there is no compelling reason for it to be a rich text field.  I will change it to plain text and see if that solves the problem, although the other solution posted above is a nice alternative if rich text should prove a requirement.

There are two solutions to this issue.

 

I was able to switch the source field in SharePoint to plain text, and in doing so, stopped the additional text from rendering in PowerBI.  This is a quick fix solution that solved my immediate need.

 

However, the other recommended solution posted above is a more robust approach if you need to retain rich text.  I can see a scenario where I may want to retain text bullets, so I will experiment with the other approach as a long term fix to evolving my dashboard.

 

Thank you!!!

View solution in original post

Hey all - in case anyone comes across this, I want to point out that there are some easier ways to handle this now. I've written it up in a blog post here - https://whitepages.unlimitedviz.com/2018/04/power-bi-report-rich-text-sharepoint/

 

Anonymous
Not applicable

@diverdown1964 - lovely piece of information, thank you, found it very useful.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors