Advice Regarding Tables

[Tufail]

Hi, knowledgeable Members

I need some advice regarding handling large tables. (300+ Columns)

I have 4 tables in xls that hold different pieces of information. I am building a semantic model in Power BI to reuse for a bunch of different reports, so I don't want to remove the columns that I don't need right now in case I might need them later on. I also need to make some conditional columns. Should I do this in the fact table or create a new table (merge as new) with the columns that I need and add them to this table? Finding the columns that I need in such a long list is confusing.

I would appreciate any advice on what is best for performance and scalability.

[Sergii24 · Accepted solution]

Hi @Tufail, I expect that many of my fellow community member will agree with the following advice: don't import columns "you might need later". Load as less data as possible to achieve the objective of a single report: having a one single report answering all business questions might be a very risky strategy as your report might have very poor performance.

I know that such answer might be different from what you expect, but this is the advice, if you really looking for it 🙂

Now, if you want to combine 4 excels to have unique table to use within multiple reports, you might try to use "Dataflows". Then, based on specific needs, you load only minimum necessary columns to a single report. Even in this case I encourage you to review all 300 columns and think whether you really would need all of them. Removing even 100 would be a great starting point!

Have a great day and good luck! 

[Tufail]

Hi @Sergii24 

Thanks you so much for your response.
Most of the reports will need to be refreshed daily so I am trying to reduce the number of places to refresh. 

Nonetheless, advice taken. I was able to bring the fact table down to 186. I will reduce the other tables hereafter. I suppose at a later stage I can reduce it further and ultimately reduce the number of columns during the extraction process.

Once again, thank you.

[Sergii24]

I'm glad that yo found it helpful! Please remember to accept it as solution if you believe the topic is closed. Have a great day!