How can I reduce the space used by a text column in Power BI?

[ansa_naz]

Hi all

I have a column in a table in PBI Desktop - the table is Called 'Jobs' and the column is 'JobID'. The JobID column is a datatype of Text when I view it in the Power Query screen. The length of the field is always 6 characters. It is obtained from a SQL View import query - in the SQL view the column is datatype NVARCHAR(10)

Currently, this column takes 7% of the Power BI Database size, and 15% of the Power BI table size (when using Vertipaq Analyzer) - the stats are:

Row Labels

Cardinality

Table Size

Columns Total Size

Data Size

Dictionary Size

Columns Hierarchies Size

Encoding

User Hierarchies Size

Relationships Size

Table Size %

Database Size %

Segments #

Partitions #

Columns #

JobID

111,702

4,475,444

297,880

3,283,900

893,664

HASH

15.35 %

7.04 %

1

1

1

Does anyone have any ideas how I could go about reducing its size? As it is the unique ID field for this table, I am not sure how I can go about reducing its size? Also, not sure why it would even be that big, as it is only 6 characters for each record.

Cheers for all help!

[Kidata]

Yeah, having the same issue as my ids includes letters and numbers, so need to find a way to iptimize it on power bi side. od not find the solution yet.

[v-yuta-msft]

@ansa_naz ,

This is a question about space optimization in SQL Server, I would suggest you to refer to doc below:

https://docs.microsoft.com/en-us/sql/database-engine/memory-optimized-table-variables?view=sql-serve...

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ansa_naz]

Hi @v-yuta-msft I dont need to reduce the space usage in SQL, I am more interested in whether I can reduce the space usage for this column in Power BI? The space stats I have added in my original post are for the Power BI file where this column is saved - I have amended my original post to make this clearer. Cheers