Data keeps losing once I delete the queries and connections

[Dini_03]

Hello everyone! 

I am just new to my job and I currently facing this issue. I have a report previously done by my colleague (which consists of pivot tables, measures, power queries, and connections) that needs to be submitted to my boss every month. 

However, I found out that the file size is too big to be sent through email and will lag once someone tries to open the excel file. 

So the question is: 

1. I tried to solve this file size by deleting the power queries/connections but it causes my pivot table to lose its data. How can I resolve this? My boss just needs to see the pivot table only and not the queries. 

[bcdobbs]

This sounds like excel and not power BI?

That said you can't have one without the other and keep it dynamic. You'd have to copy and paste values to a new file to see a static version.

You'd be better to look at the data power query is bringing in:

1) Are some tables only being used as staging queries for later down the power query line. If so mark them as do not load.

2) Are there any columns that are not used in the pivot table, remove them.

3) Are you loading data over all time, can you filter to just the last year or so?

More involved solutions:

4) I'm guessing you're loading all data to tables and then building tables. Similar to 1) for the tables you need mark them as load to data model rather than a table. I suspect that will reduce the size but you'd then have to rebuild pivots.

5) If your company has power bi licences move the whole lot into power bi so you're not shipping files around. Your power query will be transferable.

Hope some ideas help.