What is an example of practical scenario when we might need to disable 'Enable load' of the table in Power Query editor? Does it work the same as deleting the table from the entire data model? If so, why keeping a weed?
Asked
Active
Viewed 6,664 times
3 Answers
7
@teylyn is correct. One of the primary uses for these tables is for storing tables that are only used for building up the ones that you actually want to load into your model.
For example, if you have two tables A and B but you're really interested in their union C = A ∪ B, then you can append A and B in the query editor to create a new table C (not a DAX union after loading) and only load that table to your model by unchecking 'Enable load' on A and B.
This helps keep your model cleaner and your PBIX smaller since the intermediate staging tables are only stored as connections rather than having to store the data they contain too. Tables that aren't loaded to the model obviously can't be used in visuals or referenced outside of the query editor but they behave just like any other table within the query editor.
Alexis Olson
- 38,724
- 7
- 42
- 64
-
I was missing the detail you mentioned that disable load works only with M, and does not work with DAX tables. – Przemyslaw Remin Jun 15 '20 at 19:07
-
@PrzemyslawRemin, well, I said Power Query. – teylyn Jun 15 '20 at 20:31
-
@teylyn If Alexis had not clearly pinned that DAX is not in the game for that option, I would have thought that it does not work as expected. I am grateful for your answer because it assured about the expected behavior of the option though I could not get it work. – Przemyslaw Remin Jun 16 '20 at 09:46
-
@PrzemyslawRemin I disagree. Your question title and body text refers to Power Query, not DAX. The setting is **in** the Power Query editor, not in DAX. My answer is about the Power Query editor, not DAX. You cannot fault the messenger if you misinterpret the message and apply it to something it doesn't apply to. – teylyn Jun 16 '20 at 20:57
-
@PrzemyslawRemin and by definition, anything done with DAX is already **in** the data model. – teylyn Jun 16 '20 at 21:19
3
Power BI will perform all Power Query transformations before the data is loaded into the data model. You can have intermediary queries that you need to arrive at a certain data state, but you may not use some of these intermediary queries as the data source for visualizations in the report. In that case, you can disable the load of these queries into the data model.
This preserves memory and can improve performance.
teylyn
- 34,374
- 4
- 53
- 73
-
"intermediary queries" - that's what I thought they were. I thought that these are tables which cannot be accessed in visuals but I hoped they can be accessed by another "end" queries. I thought of a scenario where I have A and B intermediary queries which produce C=UNION(A,B) which is "end" query. But as soon as I disable load of A I can't use it for C. – Przemyslaw Remin Jun 15 '20 at 07:02
-
if this solves your issue, please mark the answer as explained in the [tour] – teylyn Jun 15 '20 at 20:31
-
Remin, what do you mean by "can't use it for C". For example - suppose in power query you merge or combine tables A and B to produce table C, and then you uncheck enable load option on A and/or B then do you mean C will no longer work? Can you explain please. – variable Jan 17 '21 at 08:15
0
Another example, where a rejig of the load/refresh is needed for changes to a static table sourced from a spreadsheet.
Column order within Power Query is not reflected in Model or Datasheet views (bug?)
Gary Thomann
- 576
- 6
- 17