I have a column in DQ that is stored in HTML format

[ukeasyproj]

Is there any way to convert this column to regular text?

[Anonymous · Accepted solution]

Hi @ukeasyproj,

In your scenario, you would need to firstly remove HTML tags in SQL Server data source, then load data from SQL Server database to Power BI using DirectQuery mode. Please check the following detailed steps.

1. Create a function in your database using the following codes.

IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL 
   DROP FUNCTION dbo.RegexReplace
GO
CREATE FUNCTION dbo.RegexReplace
(
  @pattern VARCHAR(255),
  @replacement VARCHAR(255),
  @Subject VARCHAR(MAX),
  @global BIT = 1,
 @Multiline bit =1
)
RETURNS VARCHAR(MAX)

AS BEGIN
DECLARE @objRegexExp INT,
    @objErrorObject INT,
    @strErrorMessage VARCHAR(255),
    @Substituted VARCHAR(8000),
    @hr INT,
    @Replace BIT

SELECT  @strErrorMessage = 'creating a regex object'
EXEC @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp OUT
IF @hr = 0 
    SELECT  @strErrorMessage = 'Setting the Regex pattern',
            @objErrorObject = @objRegexExp
IF @hr = 0 
    EXEC @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
IF @hr = 0 /*By default, the regular expression is case sensitive. Set the IgnoreCase property to True to make it case insensitive.*/
    SELECT  @strErrorMessage = 'Specifying the type of match' 
IF @hr = 0 
    EXEC @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
IF @hr = 0 
    EXEC @hr= sp_OASetProperty @objRegexExp, 'MultiLine', @Multiline
IF @hr = 0 
    EXEC @hr= sp_OASetProperty @objRegexExp, 'Global', @global
IF @hr = 0 
    SELECT  @strErrorMessage = 'Doing a Replacement' 
IF @hr = 0 
    EXEC @hr= sp_OAMethod @objRegexExp, 'Replace', @Substituted OUT,
        @subject, @Replacement
 /*If the RegExp.Global property is False (the default), Replace will return the @subject string with the first regex match (if any) substituted with the replacement text. If RegExp.Global is true, the @Subject string will be returned with all matches replaced.*/   
IF @hr <> 0 
    BEGIN
        DECLARE @Source VARCHAR(255),
            @Description VARCHAR(255),
            @Helpfile VARCHAR(255),
            @HelpID INT

        EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT,
            @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT
        SELECT  @strErrorMessage = 'Error whilst '
                + COALESCE(@strErrorMessage, 'doing something') + ', '
                + COALESCE(@Description, '')
        RETURN @strErrorMessage
    END
EXEC sp_OADestroy @objRegexExp
RETURN @Substituted
END
Go

2. Then use this function to  remove HTML tags from your column, there is an example four reference. My original table looks like below.

3. Change configure settings using the following codes, otherwise you will get error message “SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server” when running Update statements in Step 4.

sp_configure'show advanced options', 1 
GO 
RECONFIGURE; 
GO 
sp_configure'Ole Automation Procedures', 1 
GO 
RECONFIGURE; 
GO 
sp_configure'show advanced options', 1 
GO 
RECONFIGURE;

4. Update the HTML field using the function created in Step 1.


5. Import data from SQL Server database to Power BI Desktop.

Thanks,
Lydia Zhang

[MarcelBeug]

Coincidentally, recently I created a recursive function (PowerQuery / M) to remove HTML tags.

It has 4 arguments (of which 2 optional to run the function in test mode):
String,
Startposition (typically 0 to start with)
Optional current iteration (typically 1 to start with)
Optional maximum number of iterations.

        fnRHTMLT = (String as text, StartPosition as number, optional Iteration as number, optional MaxIterations as number) as text =>
        let
            StringFromStartposition = Text.RemoveRange(String, 0, StartPosition),
            StartPositionEndTag = Text.PositionOf(StringFromStartposition, "</"),
            PositionsEndTag = if StartPositionEndTag = -1 
                                then -1 
                                else Text.PositionOf(Text.RemoveRange(StringFromStartposition, 0, StartPositionEndTag),">"),
            StartTag = if PositionsEndTag = -1 
                       then null 
                       else "<" & Text.Range(StringFromStartposition, StartPositionEndTag + 2, PositionsEndTag - 1),
            StartPositionStartTag = if PositionsEndTag = -1 
                                    then -1 
                                    else Text.PositionOf(Text.Start(String,StartPosition + StartPositionEndTag),StartTag,Occurrence.Last),
            NewString = if StartPositionStartTag = -1 
                        then String 
                        else Text.RemoveRange(Text.RemoveRange(String,StartPosition + StartPositionEndTag, PositionsEndTag + 1),StartPositionStartTag, PositionsEndTag),
            NextStartPosition = if PositionsEndTag = -1
                                then -1
                                else if StartPositionStartTag = -1
                                     then StartPosition + StartPositionEndTag + 1
                                     else StartPosition + StartPositionEndTag - PositionsEndTag,
            Result = if NextStartPosition = -1
                     then NewString
                     else if Iteration = null 
                          then @fnRHTMLT(NewString, NextStartPosition)
                          else if Iteration = MaxIterations
                               then NewString
                               else @fnRHTMLT(NewString, NextStartPosition, Iteration + 1, MaxIterations)

        in
            Result

[ukeasyproj]

@MarcelBeug

Hey, I am new to power bi, in the query editor, where would I need to insert your code?

[MarcelBeug]

You can incorporate the code in your query in the Advanced Editor.

Example:

let
    <Insert the function here followed by a comma>,
    TableWithDescription = Table.FromList({"<BODY><p>MarcelBeug</p></BODY>"},null,type table[Description = text]),
    #"Added Custom" = Table.AddColumn(TableWithDescription, "HTMLRemoved", each fnRHTMLT([Description],0))
in
    #"Added Custom"

[ukeasyproj]

@MarcelBeug

This is my current query as folllows:

let
    Source = Sql.Database(ServerName, DatabaseName),
    dbo_TT_Projects = Source{[Schema="dbo",Item="TT_Projects"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_TT_Projects, each [AccountId] = AccountID),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ProjectID", "Name", "EndDate", "ActCompletionDate", "Duration", "StartDate", "CreationDate", "ProjectStatusID", "BillingType", "BillingAmount", "PriorityID", "Progress", "CustomerID", "CreatorID", "EstimatedHours", "Description", "Budget", "PortfolioID", "Billed", "MD_ProjectFields"}),
    #"Expanded MD_ProjectFields" = Table.ExpandTableColumn(#"Removed Other Columns", "MD_ProjectFields", {"ProjectDECIMAL0", "ProjectDECIMAL1", "ProjectDECIMAL2", "ProjectDECIMAL3", "ProjectDECIMAL4", "ProjectDATETIME0", "ProjectDATETIME1", "ProjectDATETIME2", "ProjectDATETIME3", "ProjectDATETIME4", "ProjectDATETIME5"}, {"ProjectDECIMAL0", "ProjectDECIMAL1", "ProjectDECIMAL2", "ProjectDECIMAL3", "ProjectDECIMAL4", "ProjectDATETIME0", "ProjectDATETIME1", "ProjectDATETIME2", "ProjectDATETIME3", "ProjectDATETIME4", "ProjectDATETIME5"}),
    #"Renamed CustomFields" = Table.RenameColumns(#"Expanded MD_ProjectFields",{{"ProjectDATETIME1", "Original End Date"}, {"ProjectDATETIME2", "Q1 End Date"}, {"ProjectDATETIME3", "Q2 End Date"}, {"ProjectDATETIME4", "Q3 End Date"}, {"ProjectDATETIME5", "Q4 End Date"}, {"ProjectDECIMAL0", "Original Estimated Hours"}, {"ProjectDECIMAL1", "Q1"}, {"ProjectDECIMAL2", "Q2"}, {"ProjectDECIMAL3", "Q3"}, {"ProjectDECIMAL4", "Q4"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Renamed CustomFields",{{"Name", "Project Name"}, {"StartDate", "Start Date"}, {"EndDate", "End Date"}, {"ProjectDATETIME0", "PO ACK DATE"}})
in
    #"Renamed Columns"

Still confused where I should put your function, the column I want to invoke it on is called Description

[MarcelBeug]

You can put the code between "let" and "Source". And don't forget to put a comma after "Result".

Now you can use the function for instance by adding a custom column with code: fnRHTMLT([Description],0)

[ukeasyproj]

let

   fnRHTMLT = (String as text, StartPosition as number, optional Iteration as number, optional MaxIterations as number) as text =>
        let
            StringFromStartposition = Text.RemoveRange(String, 0, StartPosition),
            StartPositionEndTag = Text.PositionOf(StringFromStartposition, "</"),
            PositionsEndTag = if StartPositionEndTag = -1 
                                then -1 
                                else Text.PositionOf(Text.RemoveRange(StringFromStartposition, 0, StartPositionEndTag),">"),
            StartTag = if PositionsEndTag = -1 
                       then null 
                       else "<" & Text.Range(StringFromStartposition, StartPositionEndTag + 2, PositionsEndTag - 1),
            StartPositionStartTag = if PositionsEndTag = -1 
                                    then -1 
                                    else Text.PositionOf(Text.Start(String,StartPosition + StartPositionEndTag),StartTag,Occurrence.Last),
            NewString = if StartPositionStartTag = -1 
                        then String 
                        else Text.RemoveRange(Text.RemoveRange(String,StartPosition + StartPositionEndTag, PositionsEndTag + 1),StartPositionStartTag, PositionsEndTag),
            NextStartPosition = if PositionsEndTag = -1
                                then -1
                                else if StartPositionStartTag = -1
                                     then StartPosition + StartPositionEndTag + 1
                                     else StartPosition + StartPositionEndTag - PositionsEndTag,
            Result = if NextStartPosition = -1
                     then NewString
                     else if Iteration = null 
                          then @fnRHTMLT(NewString, NextStartPosition)
                          else if Iteration = MaxIterations
                               then NewString
                               else @fnRHTMLT(NewString, NextStartPosition, Iteration + 1, MaxIterations)

        in
            Result,



    Source = Sql.Database(ServerName, DatabaseName),
    dbo_TT_Projects = Source{[Schema="dbo",Item="TT_Projects"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_TT_Projects, each [AccountId] = AccountID),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ProjectID", "Name", "EndDate", "ActCompletionDate", "Duration", "StartDate", "CreationDate", "ProjectStatusID", "BillingType", "BillingAmount", "PriorityID", "Progress", "CustomerID", "CreatorID", "EstimatedHours", "Description", "Budget", "PortfolioID", "Billed", "MD_ProjectFields"}),
    #"Expanded MD_ProjectFields" = Table.ExpandTableColumn(#"Removed Other Columns", "MD_ProjectFields", {"ProjectDECIMAL0", "ProjectDECIMAL1", "ProjectDECIMAL2", "ProjectDECIMAL3", "ProjectDECIMAL4", "ProjectDATETIME0", "ProjectDATETIME1", "ProjectDATETIME2", "ProjectDATETIME3", "ProjectDATETIME4", "ProjectDATETIME5"}, {"ProjectDECIMAL0", "ProjectDECIMAL1", "ProjectDECIMAL2", "ProjectDECIMAL3", "ProjectDECIMAL4", "ProjectDATETIME0", "ProjectDATETIME1", "ProjectDATETIME2", "ProjectDATETIME3", "ProjectDATETIME4", "ProjectDATETIME5"}),
    #"Renamed CustomFields" = Table.RenameColumns(#"Expanded MD_ProjectFields",{{"ProjectDATETIME1", "Original End Date"}, {"ProjectDATETIME2", "Q1 End Date"}, {"ProjectDATETIME3", "Q2 End Date"}, {"ProjectDATETIME4", "Q3 End Date"}, {"ProjectDATETIME5", "Q4 End Date"}, {"ProjectDECIMAL0", "Original Estimated Hours"}, {"ProjectDECIMAL1", "Q1"}, {"ProjectDECIMAL2", "Q2"}, {"ProjectDECIMAL3", "Q3"}, {"ProjectDECIMAL4", "Q4"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Renamed CustomFields",{{"Name", "Project Name"}, {"StartDate", "Start Date"}, {"EndDate", "End Date"}, {"ProjectDATETIME0", "PO ACK DATE"}}),
    #"Added Custom" = Table.AddColumn(dbo_TT_Projects, "HTMLRemoved", each fnRHTMLT([Description],0))

in
    #"Added Custom"

I did the following but got an error saying it is not supported in DirectQuery Mode

[MarcelBeug]

Sorry, I didn't take into account any Direct Query limitations.

I can't think of any alternative that would work in DQ mode.
Maybe something could be done in your database environment, but that would be outside my scope.