HTML Tags versus PowerBI

[Anonymous]

Hello people,

I'm trying to solve a problem in my report.... There are a lot of content with HTML Tags in my date and I tried many ways that I found here at community and I was not able to solve the problem.

The last error was: We cannot convert a value of type List to type Text.

I attached the PBIX file for easy understanding what I'm talking about. I would thank a lot who can help me to solve this case.

PBIX: https://drive.google.com/open?id=13Qu_TDITkLAKj4pW7CvrsNl4v1-qMk_a

Thank you!

[VijayP]

Since the text in Spanish may be not able to make it out. 

There are Three Rows the table you have given in PBIX, Do you require all rows , in other words need to know which part the text you want. I can remove <p>,</p>,</strong> etc by just using replace values option in power query editor. it would be little long process but too long. Based on your reply should be able to answer you.

[Anonymous]

Hello @VijayP

I want just the simple text between any HTML tag in the data.

For example, in the text bellow:

<p>Hi, my name is <span style="color:red">Jhon</span> and I'm 35.</p>

I'd like to remove the tags and show the text as: Hi, my name is Jhon and I'm 35.

This transformation would be each line of my PBIX.... The pbix that I've uploaded has just 3 lines, but my real database contain more than 100000 rows.

Do you think it is possible?

Thank you very much for your attention 🙂

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this code on New Source -> Blank Query -> Advanced Editor:

let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVJNj9MwEP0rQ08gtU23i7poKw5DYhajJC5xWiG2K+Sm3tRSmkRxuqj/nnFcRNkLByeeeeM3bz4eH0fb02x2W7TDT6eNtQqOujo0HRRN3XdmdzIOmy+KQu9NtfSG6k2118sGvKlVcer1ErTtVQdtp62uez2mGwUW5FIEUYgCsl/U1CcNLllHT+NXOjDMWMRzAd/WDBKRsDQXEiIGYcYlfUUiAAeQyRwTwjZ8w9JIEJRK9rDmzocpxlxiBo4HiQKkcP6MxWxDNpPAgCc+s21VDbY/V/rjdlQ0VdPdd+Xu7d3dGD7c0Fm823qNeiWyfHjta693tl2uMEMg6jDmzEFTSIWUAvIMP2H8RcAGOSRrVxLGLAFSmrDQE2BhumKZ8pB8yKXDeLLC8FK+O44MqSD/IPSTEKQfQ8wyliOkSC2IKIbOtS5GKpDkRGzDXO2f8QfLIMQIgadyqMO30z9a+0EOYhgMrY4F5SAFEK8f6BJ5NVe1UqBLzL6vWEaekJPhRFNIwqRLelGEfmmIidofCTm9EnrZCDcF3+X/rUjRdJ0pTb1v7kHs6Kr2zfgyyr5r6tKHwddTZVRd6zfgQXMsQVU9DVk6BttsR3DQpjw41/yWLNsVdA3a6kT0NrBHU+lzYI6q1DaYTWzlgqvzxAGmLifPqtA/b54X7+fTltKOoDf9sEV/E/wy+/7whz/4p8Brsa9rfvoN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [comentario = _t]),
#"Added Custom" = Table.AddColumn(Fonte, "Custom", each Text.Combine
(List.Transform(
Text.Split([comentario], "<"),
each Text.AfterDelimiter(_, ">")), " "))
in
#"Added Custom"

Did I answer your question? Mark my post as a solution!
Ricardo

[Anonymous]

Hi @camargos88 .

That's works perfectly! Thank you!

I have just a question:

1 - Is there any way to decode this part of data that I put below in red? For example, this part of text would be: "Nossa melhor contribuição"

Thank you again and again 🙂

[Anonymous]

@camargos88 - I got it 🙂

I found the code below and it works to encode/decode html texts. Thank you 😉

(Text as any) => let
Source = Text,
Custom1 = Web.Page(Source),
Data = Custom1{0}[Data],
Children = Data{0}[Children],
Children1 = Children{1}[Children],
Children2 = Children1{0}[Children],
Text1 = Children2{0}[Text]
in
Text1

[camargos88]

@Anonymous ,

With this code you dont need to split any text! Good one!

Ricardo

[camargos88]

@Anonymous ,

What are you trying to do ?

Você é brasileiro ?

Ricardo

[Anonymous]

@camargos88 

Sim, sou brasileiro. Estou tenteando remover as tags html, deixando apenas o texto legível. 

Já tentei de inúmeras formas que mencionam aqui na comunidade e em outras, e não obtive sucesso.

[camargos88]

@Anonymous ,

Qual é a sua fonte ?

Pergunto pois talvez seja mais fácil pra interpretar dentro das tags.

Ricardo

[Anonymous]

@camargos88 - conecto num MySQL (só acesso leitura) e os dados são oriundos dele. 

[VijayP]

@Anonymous 

I have seen the pbix and found two elements 

1. A Table

2. another a query with errors

I think you want data to be extracted from Table to another table?

Please let me know what is your final output should be . so that I can help you how to deal with it

[Anonymous]

Hi @VijayP 

I have just created a short of mine report to explain the problem.

One table is how the data is on my PBIX. The another contain a query (with errors) that I tried to solve.

What I need is to remove the HTML, but I couldn't solve by myself.

I appreciate you attention and if you could help me, I'd thank you a lot.