Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
datasetleo
Helper I
Helper I

Power Query - Excel - remove data before saving workbook

Hi, all!

 

Happy New Year. Thanks to everyone who has kindly supported me all these months!

 

One more question: 

 

I am looking to dump data before saving a file so that I can reduce its file size. 

 

Under query properties, I see a remove data from external data range before saving the workbook, but it is grayed out, even if I have its dependant box ticked.

 

How can I achieve that? keeping queries, but no data so file size is smaller; then, upon opening file, either loading data automatically or having the user option for "refresh".

 

Thanks!

 

 

d.png

 

1 ACCEPTED SOLUTION

Hello @datasetleo 

 

what you could try is something like this
- create boolean parameter

- in the last steps of all your queries add a new step with an if-statement. in case the boolean is true hand over your real data, if its's false then only a empty table without any data, but with the correct columns

 

in this way you would be able to change the parameter and to empty your complete data model

What do think about it?

 

Jimmy

View solution in original post

5 REPLIES 5
Jimmy801
Community Champion
Community Champion

Hello @datasetleo 

 

a query itself needs no diskspace whatsoever. Diskspace is only needed to store the output data of an query. Normaly you get rid of all data before storing it anyway. So I don't know what could be the right approach to reduce diskspace in your case. I would consider storing all output data in power pivot, as this has a different data storing concept, and it take muuuuch lesser diskspace.


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi, @Jimmy801  - Thanks for the reply.

 

I have several queries in the datamodel, and it seems like that is why the query data is being stored in the local file.

In other Excel apps, what I have done is to query one item and save it, but this is not the case for the one I am working on.

 

I was looking for a way to dump/clean datamodel data so that file is just "virgin"; 

 

let me know any thoughts. Thanks!

Hello @datasetleo 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

Hello @datasetleo 

 

what you could try is something like this
- create boolean parameter

- in the last steps of all your queries add a new step with an if-statement. in case the boolean is true hand over your real data, if its's false then only a empty table without any data, but with the correct columns

 

in this way you would be able to change the parameter and to empty your complete data model

What do think about it?

 

Jimmy

My Power Queries have a solution like this enabled. If you don't value the speed of the refresh, you can just put something like this at the end of your query:

 

= if getValue("rngDummyEnabled") <> "y" then Source else Table.FirstN(Source,0)

Full query code:

let
    Source = CostHistory,
    #"Kept First Rows" = if getValue("rngDummyEnabled") <> "y" then Source else Table.FirstN(Source,0)
in
    #"Kept First Rows"

"Source" is the name of the previous query row and "getValue" is a function query (create a blank query and paste code below), which extracts the value of any "named range" field in any sheet:

(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

 

If you do value speed, consider creating a specific dummy query, which outputs all the columns but no rows, whithout processing the actual files.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors