expanding custom column from Html column

Question

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

ryan_mayu · Answer

maybe you can try this in PQ
&nbsp;
= Table.AddColumn(#"Promoted Headers", "Custom", each Text.BetweenDelimiters([NeedtoKnow],".","&gt;",{0,RelativePosition.FromEnd},{0,RelativePosition.FromEnd}))
&nbsp;

anonymous · Answer

Thanks for the reply from&nbsp;ryan_mayu&nbsp; and lbendlin&nbsp;, please allow me to provide another insight:
Hi,&nbsp;jcastr02&nbsp;Could you please let me know if the responses from&nbsp;ryan_mayu&nbsp; and lbendlin have resolved your issue? If it did, kindly accept them as the solution.&nbsp;
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&nbsp; into your custom column, the results in the advanced editor will appear as follows, which may lead to inconsistent output.

&nbsp;I recommend modifying it to the following:
Text.BetweenDelimiters([NeedtoKnow],".","&gt;",{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:

&nbsp;
Please find the attached pbix relevant to the case.
&nbsp;
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
&nbsp;
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
&nbsp;
&nbsp;

lbendlin · Answer

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

jcastr02 · Answer

Hello&nbsp;lbendlin&nbsp;- 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 "".IdLOBSubjectNeedtoKnowExpected Outcome248Central UtilityCRSM: Change Healthcare Cyber Security Issuenull&nbsp;249Central UtilityCRSM: Change Healthcare Cyber Security Issuenull&nbsp;250Central UtilityCRSM: Change Healthcare Cyber Security Issuenull&nbsp;251Central UtilityCRSM: Change Healthcare Cyber Security Issuenull&nbsp;252Central UtilityCRSM: Change Healthcare Cyber Security Issuenull&nbsp;253TestThis is a test of the communication application&lt;div class="ExternalClass2A5FD88EB9564E91B94F781A7C0166CE"&gt;&lt;p&gt;This will be the need to know section.&lt;/p&gt;&lt;/div&gt;This will be the need to know section254TestThis is a test of the communication application&lt;div class="ExternalClass9BE4188F1B224A2AB16E5580E9D5AA98"&gt;&lt;p&gt;&lt;span style="font-size&amp;#58;11pt;"&gt;&lt;span&gt;&lt;span style="font-size&amp;#58;15.0pt;"&gt;This will be the need to know section.&lt;/span&gt;&lt;/span&gt;&lt;/span&gt;&lt;/p&gt;&lt;/div&gt;This will be the need to know section.256PCCREMINDER: SOP 2.10.1 Linking Buyout Profiles&lt;div class="ExternalClassB797D798C5B44B18AB82CE2DB706A4E2"&gt;&lt;p&gt;&lt;span style="font-size&amp;#58;11pt;"&gt;&lt;span&gt;When acquires a competitor pharmacy, the patient profile/prescription records can be located in the “Buyout Search” tab in POWER.&lt;/span&gt;&lt;/span&gt;&lt;/p&gt;&lt;/div&gt;When acquires a competitor pharmacy, the patient profile/prescription records can be located in the “Buyout Search” tab in POWER.

lbendlin · Answer

This is bad HTML. As a minimum add a class tag to the inner span, and make the usage of span consistent.
&nbsp;
You can use the HTML parser to get to the payload.

Forum Discussion

expanding custom column from Html column

9 Replies

Related content

Expand column M

Custom Column

Expand list with records to columns

Expand Columns and Rows

API expand column

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:11pt;"><span><span style="font-size: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: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.