Creating Custom columns

[guna_knp]

I have Dataset which has more than 20 lakh rows and around 200 + columns and the requirement is to create custom column on these 200+ columns (may be more than 200 custom columns) to check the quality of data and create visualization, so my question is can we build 200+ custom columns in Power BI and build visualization? is Power BI a feasible solution? 

[Akash_Varuna · Accepted solution]

Hi , 

Yes, Power BI can handle large datasets and create 200+ custom columns, but it may lead to performance issues like slow refreshes and heavy model size. To manage this effectively, preprocess the data using tools like SQL, Dataflow,Azure Data Factory to create custom columns and streamline the dataset. In Power BI, focus on key metrics by aggregating data quality insights and avoid overloading the model with unnecessary calculated columns. Use Import Mode with optimized columns or DirectQuery if transformations are handled externally. This hybrid approach ensures scalability and better performance.

[v-saisrao-msft]

Hi @guna_knp,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you

[v-saisrao-msft]

Hi @guna_knp,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @guna_knp ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Omid_Motamedise · Accepted solution]

Yes, you can create 200+ custom columns in Power BI, but whether it's a feasible solution depends on performance, manageability, and visualization needs. Here’s what you should consider:

1. Performance Impact
Large dataset (20+ lakh rows): Adding 200+ calculated columns can significantly slow down Power BI because calculated columns are computed at the model level and increase memory consumption.
Data refresh times: The more calculated columns you create, the longer your refresh times may be, especially if they involve complex calculations.

2. Alternative Approaches
To improve efficiency, consider these alternatives:

Use Power Query for Transformations: Instead of calculated columns in DAX, create your columns in Power Query (M language) to preprocess data before loading it into Power BI. This reduces the load on your model.

Use Measures Instead of Calculated Columns: If your columns are meant for visualization, try using measures instead of calculated columns, as measures are computed dynamically and consume less memory.

Use Aggregation & Summarization: If not all 200 columns are needed at once, consider aggregating data to reduce the model size and enhance performance.

[guna_knp]

Thank you, will look into the proposed approach

[Akash_Varuna]

Sure If the post helped please do give a kudos and accept this as a solution

[Akash_Varuna · Accepted solution]

Hi , 

Yes, Power BI can handle large datasets and create 200+ custom columns, but it may lead to performance issues like slow refreshes and heavy model size. To manage this effectively, preprocess the data using tools like SQL, Dataflow,Azure Data Factory to create custom columns and streamline the dataset. In Power BI, focus on key metrics by aggregating data quality insights and avoid overloading the model with unnecessary calculated columns. Use Import Mode with optimized columns or DirectQuery if transformations are handled externally. This hybrid approach ensures scalability and better performance.