Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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>
Solved! Go to 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!!!
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>
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:
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"
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.
Hi Aaron,
I am facing the same issue but in my case there is a requirement for the field to be rich text as it contains web-links. Is it not possible to have an option in Power BI to convert rich text to plain text? Like a menu option under Transform. I believe it's time for that. Many thanks.
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!!!
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/
@diverdown1964 Your article was very helpful. I managed to fixed my issues using the article . Recomend this to anyone who is facing the same issues with list column types
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
12 | |
12 |