Power Query for Excel Enable load

[Ayman Hassan]

Hello all,

Does PowerQuery for Excel has the option to stop loading some queries?

I Collect data from multiple sources and then append them to one query, then when I load the data all the queries and tables are loaded, the original and the appended tables, this results in data duplication in reports. I found an option in Power BI to enable or disable loading data for selected queries in the same file as shown in the following screenshot:

Where can I find this option in Power Query for Excel or how can I solve this problem when I append multiple queries in Excel?

Thanks

[#2 · Accepted solution]

@Ayman Hassan 

In Excel, they are all loaded by default, but you can, and probably should, change this.

1. Launch Power Query
2. Go to File, Options
3. Go to the Global section
4. Go to Data Load
5. Select the Specify custom load settings
6. Uncheck both.

This will not change existing loads though.

To do this, you change it on a per query basis in Excel, not Power Query.

In Excel, with Power Query closed, go to the Data tab, and select Queries and Connections so the query list pops out in the right hand pane.

Right-click on each query and select Load To..., then...

• Select connection only - this will not load anywhere, but would be used as a query for use by other queries in Power Query
• Table - this will load the query into an Excel Table
• DataModel (checkbox at bottom) this will load the data into the data model for use by PowerPivot or CUBE functions.

There are other options, like loading directly to a Pivot Chart, but  you get the idea.

Note: if you have anything loaded anywhere and you change it, it will potentially delete data. Data in a table for instance will delete the table entirely if you change it to a connection. Data in the datamodel will vanish and trash any measures that rely on it if you uncheck the DataModel box.

[#3]

I will try and give feedback

Thanks!