How to handel RTF columns using Power Query

[karsb23]

Dear all,

I use the ODBC data source to connect to a Firebird database.

This database contains a fact-table called AT_DOCUMENT which has a column (GC_INFORMATIE) with a BLOB (binary) value.

I was able to transfromt the BLOB value to a string using the following code:

= Table.TransformColumns(Bron,{{"GC_INFORMATIE",Text.FromBinary},{"REPLACE",Text.FromBinary}})

This results for example in the following value:

"{\rtf1\ansi\deff0{\fonttbl{\f0\fnil Segoe UI;}}
\viewkind4\uc1\pard\lang1043\f0\fs18 This is a note\par
}
"

I searched all over the internet but I cannot seem to find (1) a way to remove the RTF-tags or (2) a way to use the formatted text in a table.

I have limited knowledge about languages outside of Power Query M or Dax but I tried to convert the data from BLOB in the following SQL statement, unfortunately I still get the string with the RTF-tags

SELECT  D.GC_INFORMATIE,
        CAST(SUBSTRING(GC_INFORMATIE FROM 1 FOR 32000) AS VARCHAR(32000)) AS GC_INFORMATIE2
FROM    AT_DOCUMENT D

Can someone help me out with this?

Many thanks in advance.

[Syndicate_Admin]

Dangit gents, I have the same challenge! 

Will update if I find something aswell.. 

[karsb23]

We are now setting up an datawarehouse in order to transform the data to plain text instead of doing it all in Power Query.

[veikko]

Hello,

I am facing the same problem and I haven't found any solution to that yet. @karsb23 did you figure this out already? 

[karsb23]

Hi @veikko,

Unfortunately no. If I figure something out, I will let you know.

[veikko]

Hello @karsb23, I saw your comment about setting up a datawarehouse solution in order to get rid of the RTF tags. Did you manage to get only the plain text with that solution? If so, I would be interested to hear about the solution in general level. 

[karsb23]

Dear @v-yalanwu-msft ,

Thank you for your response.

I have the type detection allready enabled and the type of the GC_INFORMATIE is set to TEXT.

Unfortunately this does not process the RTF-tags:

Any other ideas where I can find the solution?

Thank you again!

[v-yalanwu-msft]

Hi， @karsb23 ；

First, please check if the "Type Detection" has been set as "Always detect column types and headers for unstructured sources" or "Detect column types and headers for unstructured sources according to each file's setting".

If this has been set, try to detect data type manually like this:

Reference:Rich Text format issue while fetching data from pr... - Microsoft Power BI Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.