Forum Discussion
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>
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!!!
11 Replies
- sdjensen
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>
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?
- sdjensen
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"
