Blogs

Power BI > How to deal with a Text column having a length of more than 32766 characters.

Inkey, April 1, 2022 20443 Views

Power BI supports up to 32766 characters of data in a column of TEXT Data Type. What if we have more data stored in the data source?

Recently, we came across a requirement where we had to show the content of an email in HTML format in the Power BI report. The report was built in a way to showcase the latest email & it was working fine. Then we got a change request to showcase the complete email chain. It is then this issue popped up. The complete content was not shown in the visual. It was evident that if we have more than 32766 characters, then Power BI will automatically truncate the rest of the characters. After doing some research & brainstorming we came up with an approach to handle this requirement. Let’s dive into the problem and the approach, where we will guide you step by step in an easy and understandable manner.
 

Let’s look into the problem first. Here as we can see that we have a table with two rows where the number of characters in the “Message1” column for first row is 26298 and for the second it is 65532. 

And when we import this table to Power BI desktop, then it will only store the first 32766 characters for the second row as we can see below. 

As we can see, for TEXT type column, Power BI only considered the first 32766 characters.

Now, let’s look into the approach that we took to overcome this issue. There are two approaches depending on the data source that you use, i.e., if the data source is SQL Server or any other RDBMS, then we can manage this in backend itself and if we cannot do any transformation in the backend, then we can even manage this in Power Query. 

 

---

Discover the power of our services:

Microsoft Fabric, Power BI, Microsoft Business Intelligence, SQL Server, and Business Central. By the power of these services, from advanced analytics to seamless business integration, we’ve got the expertise you need to optimize operations and drive growth. Harness the potential of your data infrastructure with our comprehensive suite of solutions.

---

 

Approach 1: 
Let’s look at the first approach, where we divided the characters of the “Message1” column into multiple columns in the backend (SQL) itself as shown below. This T-SQL could certainly be improved to make it more dynamic, but to explain the concept, this simple one is fine. 

Next, we imported this table into Power BI desktop in which “Column1” will have first 32766 characters and “Column2” will have the rest of the characters as shown below.

We can split the column with large values into as many columns depending on how many characters we have in that particular column. It is always recommended to make the transformations in the backend, if possible. 

Approach 2: 

Now let’s look into the second approach where we split the column in Power Query itself. By clicking on Split Column in the Home tab, we can split the columns by number of characters and we have to mention the number of characters as 32766 and then click on Okay as shown below.  

 

As a result, Power Query will split the columns. 

As mentioned above, we need to show this in a table visual. For that, we can create a measure that concatenates Column1 & Column2 and then we can plot that measure in the table as shown below.

 

On plotting the measure, we can see how it returns the whole value of the column. 

This way we can handle this issue whichever our DataSource is. 

Let us know if there are any other approaches to handle this issue in the comments below.