Convert huge text into light data type

[Anonymous]

I have a huge text column (Visits) in my fact table which I need to later use for a distinct count measure in the report.

This column (Visits) has a lenght of 37-40 characters. It is so big that I really need to reduce its weight within the dataflow and later dataset. I have tried different things but the dataflow doesn't end up working, it always fails, it usually rans out of memory after long hours. 

I have tried to "convert" this column into a dimension table, hence I can merge into the fact table another Number column instad of the huge text column.

I've made a dataflow by connecting to the whole fact table and deleting all the other columns leaving this Visits column, and a datetime (for the later incremental refresh). Then I've deleted duplicated values on Visits column, and I've also added an index column.

Opening the dataflow with the fact table, I've connected to the dimension table and merge it with the fact table by the visits column. I've expanded the index column. Finally I've deleted the Visits column from the fact table. 

This was the plan, BUT Power BI Service can't refresh this dataflow with the merge.

Is there any other way to CONVERT this huge text column into some other type of data so it doesn't weight that much in the dataflow, so Power BI Service can refresh the dataflow and the later dataset?

[Anonymous]

• Do you need to have the text column to read or just to do a distinctcount?
• Where is the data coming from?

  Presuming you don't need to read. If the data is coming from a database that supports SQL, then create a statement that distinctcounts this coulmn to get the amount you need.

  If it's an Exceel for example. Create the index then delete the column.

  If you need to read, create a dataflow. Depending on your company's licence, Turn on Enhanced compute engine settings in the refresh part and then connect with direct query