Forum Discussion
Removing HTML Script from data in Query Editor
I transformed it into a proper function here :-) https://github.com/ImkeF/M/blob/master/Library/Text.RemoveHtmlTags.pq
Community Championjust take the original code of my function and use it as a separate function called "fnRemoveHtmlTags".
Then go back to your table and add a new step with the following code:
Table.TransformColumns(<PreviousStepName>, List.Zip({Table.ColumnNames( <PreviousStepName> ), List.Repeat({fnRemoveHtmlTags}, List.Count(Table.ColumnNames( <PreviousStepName> )))}))
You have to replace <PreviousStepName> by the name of the previous step (!! it occours 3 times in the code, but I'm not able to bold them all...)
- ImkeF
Hi Anonymous ,
did you manage to get it working? If not, this article might help you: https://www.thebiccountant.com/2019/12/18/advanced-transformation-multiple-columns-at-once-in-power-bi-and-power-query/
- ImkeF
That function didn't include any type conversion, so it cannot be the cause for it.
Unfortunately I have no idea what causes it.
Maybe you try using the other function instead.
ImkeF Thank you for asking. Yes, it works perfectly in terms of removing all of the HTML (thank you!), but now I'm receiving an "OLE DB or ODBC error: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))" message after applying the query changes. So I'm trying to figure out what's causing that. Not sure if it's related to this function in that it changes the data type and I have to handle that or what.
- ImkeF
Hi Anonymous
you can try an adjusted function that doesn't do the type-conversion:
(Table as table, Function, optional ColumnNames as list) => let columnNames = if ColumnNames = null then Table.ColumnNames(Table) else ColumnNames, Transformation = Table.TransformColumns( Table, List.Transform(columnNames, each {_, Function} ) ) in Transformation How would that change the line I added to my Advanced Editor for the original function you wrote?
#"Remove HTML Markup" = Table.TransformColumns(#"Extracted Text Between Delimiters", List.Zip({Table.ColumnNames(#"Extracted Text Between Delimiters" ), List.Repeat({fnRemoveHtmlTags}, List.Count(Table.ColumnNames(#"Extracted Text Between Delimiters" )))}))
ImkeF I went back to my report and ensured that all of the data types matched what was in Power Query Editor (in this case it's all text data from ServiceNow) and the Type Mismatch error disappeared.
Thanks for all of your help!
Super helpful, thanks Imke!
