Hi
I'm new to Power Query and Power Pivot.
I'm trying to create an dashboard fro several different tables. I have to "clean up" the tables so I've user Power Query for this. And I want to user slicers and pivot tables to combine several of the tables, so I want to use Power Pivot to help with that.
I have some problems already (and I havent even started making the dashboards 🙁 )
I have loaded several tables into Power Query and cleaned them up, and sent them to my datamodel.
Problem 1: the file is already a bit big, even before there are any charts or pivot tables visable in the file.
Problem 2: I already have memory problems, and have trouble updating my Power Query queries.
I think this can be solved with only loading the queries I'm going to use into the datamodel - is this possible?
I have about 70 queries and I only need about 25 of them in the datamodel (the rest are "helper queries", se the example below)
I'll give an example:
I have several queries that includes a project number.
* Budget
*Spending
*Payroll
etc
These queries include a lot of different information, so I haven't combined them to one query
All of them include a project number. I want to be able to use a slicer in Excel and show all information regarding the projectnumber I choose. To do this I think I need a query of all projectnumbers that are used so I can link the different queries to that query in the datamodel?
I don't have a list of all possible projectnumbers, and I wont be told when they create new ones.
So, I have to create the following queries:
* project numbers used in Budget
* project numbers used in Spending
*project numbers used in Payroll
etc
and then, combine those to one query:
* all project numbers used (and remove duplicates)
Then I have 7 queries:
* Budget
*Spending
*Payroll
* all project numbers used
* project numbers used in Budget
* project numbers used in Spending
*project numbers used in Payroll
and the only ones I actually need in the datamodel are the four on the top (in bold).
Is it possible to only import the first four into the datamodel and not the last three?
And since I already have imported them into the datamodel, is it possible to delete them from the datamodel without deleting them from Power Query?
Or is there a better way to solve this situation all together?
Thanks for any input.
- Stine -
Hi Stine,
Welcome to the forum! It sounds like you're trying to build your model before you've completed the courses 😉
In session 5 of the Power Query course I cover load options and yes, you can choose 'Connection Only' for queries that are not required in your data model. You can change the load settings by right-clicking the query in the Queries and Connections pane > Load to.
This post also offers some suggestions on improving Power Query refresh times.
I hope that points you in the right direction. Make sure you finish the courses before you get too far into building your own model, because if you set it up wrong it's not easy to undo.
Mynda
Hi
I don't think I made myself clear 
I've seen the course, but I don't see an option to chose that some of my queries goes to the datamodel and some don't?
I have tried, but when I choose "Close and Load to" I can just choose if ALL of my queries should go to the datamodel or none - I don't get an option to choose which ones of my queries should go to the datamodel and which ones should not.
Is there something I'm missing?
-Stine-
The Close & Load dialog is applied on a query by query basis on the assumption that you create a query, then close & load, then create the next query and so on.
However, if you create multiple queries and then close & load them all in one go, then whatever settings you choose are applied to all. As I mentioned above, "You can change the load settings by right-clicking the query in the Queries and Connections pane > Load to." This is done on a query by query basis.
Mynda
