Redundant tables in model

[Enigma]

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-

1. pulls the data from all 10 tables in the excel file
2. adds one calculated column in each of the 10 tables
3. 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,

[v-eachen-msft]

Hi @Enigma ,

Has your problem been solved? If the problem is resolved, you could accept the reply you like.

[v-eachen-msft]

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.

[Enigma]

This looks interesting. I shall try this and see if it works for my model.

Thanks for suggesting, @v-eachen-msft !

[DataVitalizer]

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]

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]

hi @DataVitalizer 

1. Yes. All 10 tables contain same columns. 70 columns in each table
2. A few columns that are not needed, I have removed in Query Editor, as I have mentioned in my intial post.
3. 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!

[DataVitalizer]

Hi @Enigma 

Now you can save couple steps and work on 1 table from the beginning

1- if you connect power bi to your workbook you will have to check all sheets and import them, this will display 10 tables, what I suggest you to do is to import only one sheet 🤔, Yes only 1 sheet.

You will get then the below preview

2- in the steps tab select the 1st step and you will able to expand data from all sheets without loading all sheets or append them

3- in this table you will need to add couple transformations to clean rows and keep the needed columns 

Does it work ? Mark it as a solution 

A kudos would be appreciated 

[Enigma]

Thanks for suggesting @DataVitalizer  - I will try it out and get back if it worked.

[mahoneypat]

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

[Enigma]

Thanks @mahoneypat - unchecking Enable Load seems to be the right option at the moment. There's a slight improvement.

[Enigma]

Thanks @mahoneypat 

I wasn't aware of the Enable Load option. There's also a 'Include in report refresh' toggle option.

I hope turning those off won't stop loading data in future iterations of data pull schedule. 

But thanks for suggesting.

I have a question - Does append queries happen after or before all applied steps are executed?

If it's after, then I can remove unwanted columns from each of the 10 tables and not just the 11th.

[mahoneypat]

Don't uncheck "Include in Report Refresh".  That is required for your next refresh.  Just uncheck Enable Load.

Regards,

Pat