Forum Discussion
Helper IIIRedundant tables in model
Hi,
I have several tables in my report that are redundant once the data is transformed in Query Editor. And I am looking for ways to minmise the performance impact
Here's the situation:
The source file for my report is an excel file that has 10 identical tables, one for each department. Each table with 70+ columns and approx 1500 rows which are worth a month's data. Rows will increase as future months' data is appended.
My report does the following transformations in the Query Editor-
- pulls the data from all 10 tables in the excel file
- adds one calculated column in each of the 10 tables
- does an 'Append Queries as New' operation and creates a new, 11th table which is a collection of all rows in the 10 tables.
After this, I use the data only from the 11th table. The 10 tables (with all their columns and rows) are present in the model but are useless.
Any visual/measure I update, there's a significantly high processing time before the changes are applied. This, I suspect, is due to the large 10 tables eating up bandwidth. And the data will only increase over future iterations.
I cannot merge the 10 tables into one before taking them into Power BI, as the data is too diverse and has overlaps with other tables. So the keeping them in 10 separate tables allows to distinguish the data of one table from other tables.
I deleted some unneeded columns from the tables. Will this help?
What else can I do to reduce the performance issues caused by these 10 identical tables?
10 identical tables appended in the last one
Inventory is the 11th table. All other 10 are redundant
Regards,
13 Replies
- v-eachen-msft
Community Support
Hi Enigma ,
You could create a folder and put 10 excel files into it. Then import data from folder.
Click "Combine and Transform Data", you will get one tables based on ten files. And you could keep rows/columns you need.
EnigmaThis looks interesting. I shall try this and see if it works for my model.
Thanks for suggesting, v-eachen-msft !
- DataVitalizer
Super User
Hi Enigma
I have couple questions to make it clear to me so I can help you:
1- Do all the 10 tables contain the same columns ?
2- Do yu really need all those columns data ?
3- Are there any rows you can exclude ?
Does it work ? Mark it as a solution
A kudos would be appreciated 🙂
- vanessafvg
Community Champion
hae you checked that your data types are correct? another think you can do in the query editor is off load some of the tables by doing unchecking the enable load
check this blog for more detail https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up
- Enigma
Thanks for sharing the useful resource, vanessafvg !
To answer your question, the data types are correct.
- Enigma
- Yes. All 10 tables contain same columns. 70 columns in each table
- A few columns that are not needed, I have removed in Query Editor, as I have mentioned in my intial post.
- I cannot exclude any rows. It's all production data.
The 10 tables come from 10 people from 10 departments. Earlier each department had it's own report so it worked well.
Now, our clients need a common report for all 10 departments. Hence, the need to merge data in a single report file.
I want it to make as simple as possible for them to upload their reports. All data manipulation is to happen after the data is pulled in Power BI.
So, the intent is, they will have to only dump their report in the excel without doing any changes themselves; Power BI will pull all the data, make transformations and build the common report. This is a monthly thing, and people come and go; so I want to eliminate any mistakes and make it as straightforward to dump data as possible.
Thanks for taking time to read and willing to help out. Truly appreciated!- mahoneypat
Microsoft Employee
Having 10 identical tables as a your source is fine if it is best for your business. First thing to do is to right click on each of those 10 tables and uncheck "Enable Load". That will keep them from loading into your model., but they will still be used in refresh. See how much that improve performance, but if all your analysis is only on table 11, it may not help much. Hopefully it will.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- v-eachen-msft
Hi Enigma ,
Has your problem been solved? If the problem is resolved, you could accept the reply you like.
