Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I analyze large volumes of text (infrastructure bill for example) and can highlight rows of sentences based on keywords. This is just due to how the data is structured. A simple csv file with matching keywords to each sentence. But I wanted to highlight with a bold font color, the actual keyword found within the sentence. I know there's probably a more efficient way to obtain this, but I basically just took my sentence column and keyword columns and duplicated as another column and wrapped the html code around each to display the way I wanted, then split the sentenance on the keyword getting the left and right parts, then concatenated them back together using the html font color keyword in bewteen.
I'm using the HTML visual to display by using the following:
1. Transform step that concats html for the font color of the keyword found, i.e. <b><font color='blue'> & [keyword] & </font></b>
2. Transform step that converts each sentence row into html, i.e. <html><body><p> & [sentence] & </p></body></html>
3. Then I add three custom columns splitting the text and keyword html rows. First,
Left Text = LEFT(Infrastructure2021[html], SEARCH(Infrastructure2021[keyword],Infrastructure2021[html],1,0)-1)
4. Then get the number of characters for the Right Text, which has to be pretty complex of course:
LenLeft Text = (LEN(Infrastructure2021[html]) - LEN(Infrastructure2021[Left Text])) - LEN(Infrastructure2021[keyword]) - 1
5. Now I can get the right text: Right Text = RIGHT(Infrastructure2021[html], Infrastructure2021[LenLeft Text])
6. Then I just concat my three columns with spaces in between to generate valid html that highlights each keyword.
Highlighted Text = Infrastructure2021[Left Text] & " " & Infrastructure2021[fontColumn] & " " & Infrastructure2021[Right Text]
I just add this column to the HTML visual and it displays each rows keyword with the bold blue font color!
This worked for me, but only when I put IFERROR before LEFT(column, SEARCH(column, column, 1,0)-1),column) and creating the html and fontcolumn only worked for me when I did it like this: "<html><body><p>" & [Full_Text] & "</p></body></html>".
I look forward to when this is a feature in PBI
I believe this is well worth a blog post in the community blog!
https://community.powerbi.com/t5/Community-Blog/ct-p/PBI_Comm_CommunityBlog
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |