expanding custom column from Html column

[jcastr02]

I have an HTML Column that I'm trying to convert to text.  I use the formula for custom column 
Html.Table([my column], {{"text", ":root"}}))

I do have  Blanks from the HTML Column 

When I create the custom column, the rows with blanks create an error.  If i try to replace those errors with blanks, then it doesn't let me expand the column. 

If I just expand the custom column without removing the errors - then I get error  Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]

Any help is appreciated

[jcastr02]

I had my columns as text or numbers - thus causing errors.  Once I changed the HTML column first to a text column ,then did the custom column - it worked.  Sorry for the confusion all.

[Anonymous]

Hi,@jcastr02 
We are absolutely delighted to hear that the issue has been resolved. If you’re inclined, you might consider accepting this solution, as it could also assist other community members facing the same problem, enabling them to find a resolution more swiftly.

Of course, if there is anything else we can do for you, please do not hesitate to contact us.

Looking forward to your reply.

Best Regards,

Leroy Lu

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[jcastr02]

Hello @lbendlin - see below I'm trying to convert the NeedtoKnow column to the expected outcome column - it seems the null values are causing issues (even though I replace null values with "".

Id	LOB	Subject	NeedtoKnow	Expected Outcome
248	Central Utility	CRSM: Change Healthcare Cyber Security Issue	null
249	Central Utility	CRSM: Change Healthcare Cyber Security Issue	null
250	Central Utility	CRSM: Change Healthcare Cyber Security Issue	null
251	Central Utility	CRSM: Change Healthcare Cyber Security Issue	null
252	Central Utility	CRSM: Change Healthcare Cyber Security Issue	null
253	Test	This is a test of the communication application	<div class="ExternalClass2A5FD88EB9564E91B94F781A7C0166CE"><p>This will be the need to know section.</p></div>	This will be the need to know section
254	Test	This is a test of the communication application	<div class="ExternalClass9BE4188F1B224A2AB16E5580E9D5AA98"><p><span style="font-size&#58;11pt;"><span><span style="font-size&#58;15.0pt;">This will be the need to know section.</span></span></span></p></div>	This will be the need to know section.
256	PCC	REMINDER: SOP 2.10.1 Linking Buyout Profiles	<div class="ExternalClassB797D798C5B44B18AB82CE2DB706A4E2"><p><span style="font-size&#58;11pt;"><span>When acquires a competitor pharmacy, the patient profile/prescription records can be located in the “Buyout Search” tab in POWER.</span></span></p></div>	When acquires a competitor pharmacy, the patient profile/prescription records can be located in the “Buyout Search” tab in POWER.

[lbendlin]

This is bad HTML. As a minimum add a class tag to the inner span, and make the usage of span consistent.

You can use the HTML parser to get to the payload.

[ryan_mayu]

maybe you can try this in PQ

= Table.AddColumn(#"Promoted Headers", "Custom", each Text.BetweenDelimiters([NeedtoKnow],".",">",{0,RelativePosition.FromEnd},{0,RelativePosition.FromEnd}))

[jcastr02]

@ryan_mayu Thank you - when I do this - it just reloads the values from the table.

[Anonymous]

Thanks for the reply from ryan_mayu  and lbendlin , please allow me to provide another insight:

Hi, @jcastr02 
Could you please let me know if the responses from ryan_mayu  and lbendlin have resolved your issue? If it did, kindly accept them as the solution.
 

ll's response has worked well in my example, and here are the output results:

You may want to take note of the following:

If you are using a custom column approach and directly add the code provided by ryan_mayu  into your custom column, the results in the advanced editor will appear as follows, which may lead to inconsistent output.

 I recommend modifying it to the following:

Text.BetweenDelimiters([NeedtoKnow],".",">",{0,RelativePosition.FromEnd},{0,RelativePosition.FromEnd})

If you wish to retain certain content, I suggest clicking the button in the image below and making adjustments in the advanced editing page:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVNLbtswEL3KwN26tqjoQyXoQpJpNEDTGHaCLOIsaJqOiNAUS1JN3FUO0l4uJykleZEW/SSAAxCiZjjzODPv8fp6EEZ4MByUXDlDJVw6IYXbtZ754uwYyoqqWw4fOZWuYtRwKHcrbmDBWWN8IJxa23AfrhopBzfDFi87LF4cHBgPHRgvPDDekTcvuHXtVgkLflFw3gH1BlzFgdXbbaMEo07UCqjWcv/vM5ZNEByxtfgKTFJrPywH5MFxo6gsWzvM4+kEY1JkcRKRDBVZNE0xytMyQElSkmUPwHsY3RtdFfdCSljxrgDF+RpcDXeqvgfLWXv3qE8Z6+cAY19Ib+97i96wt6wgEcJ4ioowjPIwL1BC4hgHJJvEeZ7hP/bWG1ZTBdbtJPegm1q591Z8687C5F2MTxDS7uTX/DblVRDxKHgG8qqZ/n7XCxz/piHx05yVpf/Oydnp5wmZH8PifAbhCAUjBJ+EuhPqFopmVzcOZqbeCMnt/yko0iydpBku4yKKCoTzAoclCSdFGiR5RMK3ouCq4l4s7EsjDG8V5VWkuROuNqAraraU7YbdlLXXkn+qoPuWxtrHMyN0pzbDWW3WFpivxLMia688T4pQXerT4/f9PBacGlY9Pf4AR1ft8ez8isxfTtZfubn5CQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, LOB = _t, Subject = _t, NeedtoKnow = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"LOB", type text}, {"Subject", type text}, {"NeedtoKnow", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.BeforeDelimiter(Text.AfterDelimiter([NeedtoKnow], ">"), "<")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Text.BetweenDelimiters([NeedtoKnow],".",">",{0,RelativePosition.FromEnd},{0,RelativePosition.FromEnd}))
in
    #"Added Custom1"

Please find the attached pbix relevant to the case.

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickl

[ryan_mayu]

Then you need to provide more sampele data, since the logic I used do not work for your real data.