Thursday, February 24, 2022

Add a query to an excel worksheet power query

Power Query offers several ways to create and load Power queries into your workbook. You can also set default query load settings in the Query Options window.   

Tip      To tell if data in a worksheet is shaped by Power Query, select a cell of data, and if the Query context ribbon tab appears, then the data was loaded from Power Query. 

Selecting a cell in a query to reveal the Query tab

Know which environment you're in Power Query is well-integrated into the Excel user interface, especially when you import data, work with connections, and edit Pivot Tables, Excel tables, and named ranges. To avoid confusion, it's important to know which environment you are currently in, Excel or Power Query, at any point in time.

The familiar Excel worksheet , ribbon, and grid

The Power Query Editor ribbon and data preview 

A typical Excel worksheet A typical Power Query Editor view

For example, manipulating data in an Excel worksheet is fundamentally different than Power Query. Furthermore, the connected data that you see in an Excel worksheet, may or may not have Power Query working behind the scenes to shape the data. This only occurs when you load the data to a worksheet or Data Model from Power Query.

Rename worksheet tabs    It's a good idea to rename worksheet tabs in a meaningful way, especially if you have a lot of them. It's particularly important to clarify the difference between a worksheet of data, and a worksheet loaded from the Power Query Editor. Even if you have only two worksheets, one with an Excel table, called Sheet1, and the other a query created by importing that Excel table, called Table1, it's easy to get confused. It's always good practice to change the default names of worksheet tabs to names that make more sense to you. For example, rename Sheet1 to DataTable and Table1 to QueryTable. Now it's clear which tab has the data and which tab has the query.

You can either create a query from imported data or create a blank query.

Create a query from imported data

This is the most common way to create a query.

  1. Import some data. For more information, see Import data from external data sources.

  2. Select a cell in the data and then select Query > Edit.

Create a blank query

You may want to just start from scratch. There are two ways to do this.

  • Select Data > Get Data > From Other Sources > Blank Query.

  • Select Data > Get Data > Launch Power Query Editor.

At this point, you can manually add steps and formulas if you know the Power Query M formula language well.

Or you can select Home and then select a command in the New Query group. Do one of the following.

  • Select New Source to add a data source. This command is just like the Data > Get Data command in the Excel ribbon.

  • Select Recent Sources to select from a data source you have been working with. This command is just like the Data > Recent Sources command in the Excel ribbon.

  • Select Enter Data to manually enter data. You might choose this command to try out the Power Query Editor independent of an external data source.

Assuming your query is valid and has no errors, you can load it back to a worksheet or Data Model.

Load a query from the Power Query Editor

In the Power Query Editor, do one of the following:

  • To load to a worksheet, select Home > Close & Load > Close & Load.

  • To load to a Data Model, select Home > Close & Load > Close & Load To.

    In the Import Data dialog box, select Add this data to the Data Model.

Tip   Sometimes the Load To command is dimmed or disabled. This can occur the first time you create a query in a workbook. If this occurs, select Close & Load, in the new worksheet, select Data > Queries & Connections > Queries tab, right click the query, and then select Load To. Alternatively, on the Power Query Editor ribbon select Query > Load To.

Load a query from the Queries and Connections pane 

In Excel, you may want to load a query into another worksheet or Data Model.

  1. In Excel, select Data > Queries & Connections, and then select the Queries tab.

  2. In the list of queries, locate the query, right click the query, and then select Load To. The Import Data dialog box appears.

  3. Decide how you want to import the data, and then select OK. For more information about using this dialog box, select the question mark (?).

There are several ways to edit a query loaded to a worksheet.

Edit a query from data in Excel worksheet

  • To edit a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit.

Edit a query from the Queries & Connections pane

You may find the Queries & Connections pane is more convenient to use when you have many queries in one workbook and you want to quickly find one.

  1. In Excel, select Data > Queries & Connections, and then select the Queries tab.

  2. In the list of queries, locate the query, right click the query, and then select Edit.

Edit a query from the Query Properties dialog box

  • In Excel, select Data > Data & Connections > Queries tab, right click the query and select Properties, select the Definition tab in the Properties dialog box, and then select Edit Query.

Tip      If you are in a worksheet with a query, select Data > Properties, select the Definition tab in the Properties dialog box, and then select Edit Query

A Data Model typically contains several tables arranged in a relationship. You load a query to a Data Model by using the Load To command to display the Import Data dialog box, and then selecting the Add this data to the Data Model check box. For more information about Data Models, see Find out which data sources are used in a workbook data model, Create a Data Model in Excel, and Use multiple tables to create a PivotTable.

  1. To open the Data Model, select Power Pivot > Manage.

  2. At the bottom of the Power Pivot window, select the worksheet tab of the table you want.

    Confirm that the correct table displays. A Data Model can have many tables.

  3. Note the name of the table.

  4. To close the Power Pivot window, select File > Close. It may take a few seconds to reclaim memory.

  5. Select Data > Connections & Properties > Queries tab, right click the query, and then select Edit.

  6. When finished making changes in the Power Query Editor, select File > Close & Load.

Result

The query in the worksheet and the table in the Data Model are updated.

If you notice that loading a query to a Data Model takes much longer than loading to a worksheet, check your Power Query steps to see if you are filtering a text column or a List structured column by using a Contains operator. This action causes Excel to enumerate again through the entire data set for each row. Furthermore, Excel can't effectively use multithreaded execution. As a workaround, try using a different operator such as Equals or Begins With.

Microsoft is aware of this problem and it is under investigation.

You can load a Power Query:

  • To a worksheet. In the Power Query Editor, select Home > Close & Load > Close & Load.

  • To a Data Model. In the Power Query Editor, select Home > Close & Load > Close & Load To.

    By default, Power Query loads queries to a new worksheet when loading a single query, and loads multiple queries at the same time to the Data Model.  You can change the default behavior for all your workbooks or just the current workbook. When setting these options, Power Query doesn't change query results in the worksheet or the Data Model data and annotations.

    You can also dynamically override the default settings for a query by using the Import dialog box which displays after you select Close & Load To.

Global settings that apply to all your workbooks

  1. In the Power Query Editor, select File > Options and settings > Query Options.

  2. In the Query Options dialog box, on the left side, under the GLOBAL section, select Data Load.

  3. Under the Default Query Load Settings section, do the following:

    • Select Use standard load settings

    • Select Specify custom default load settings, and then select or clear Load to worksheet or Load to Data Model.

Tip    At the bottom of the dialog box, you can select Restore Defaults to conveniently return to the default settings.

Workbook settings that only apply to the current workbook

  1. In the Query Options dialog box, on the left side, under the CURRENT WORKBOOK section, select Data Load.

  2. Do one or more of the following:

    • Under Type Detection, select or clear Detect column types and headers for unstructured sources.

      The default behavior is to detect them. Clear this option if you prefer to shape the data yourself.

    • Under Relationships, select or clear Create relationships between tables when adding to the Data Model for the first time.

      Before loading to the Data Model, the default behavior is to find existing relationships between tables, such as foreign keys in a relational database and import them with the data. Clear this option if you prefer to do this on your own.

    • Under Relationships, select or clear Update relationships when refreshing queries loaded to the Data Model.

      The default behavior is to not update relationships. When refreshing queries already loaded to the Data Model, Power Query finds existing relationships between tables such as foreign keys in a relational database and updates them. This might remove relationships created manually after the data was imported or introduce new relationships. However, if you want to do this, select the option.

    • Under Background Data, select or clear Allow data previews to download in the background.

      The default behavior is to download data previews in the background. Clear this option if you can want to see all the data right away.

See Also

Power Query for Excel Help

Manage queries in Excel

1 comment:

  1. Welcome to the future! Financing made easy with Prof. Mrs. DOROTHY LOAN INVESTMENTS

    Hello, Have you been looking for financing options for your new business plans, Are you seeking for a loan to expand your existing business, Do you find yourself in a bit of trouble with unpaid bills and you don’t know which way to go or where to turn to? Have you been turned down by your banks? MRS. DOROTHY JEAN INVESTMENTS says YES when your banks say NO. Contact us as we offer financial services at a low and affordable interest rate of 2% for long and short term loans. Interested applicants should contact us for further loan acquisition procedures via profdorothyinvestments@gmail.com

    I'm here to share an amazing life changing opportunity with you. its called Bitcoin / Forex trading options, Are you interested in earning a consistent income through binary/forex trade? or crypto currency trading. An investment of $200 can get you a return of $2,480 in 7 days of trading, We invest in all profitable projects with cryptocurrencies. We have excellent trading instruments and also support them with the best tools. Make as much as $1,000 or more every week with a starting capital of $200 to $350 You earn 100% of your initial profit every 7-14 business days and you get to do this from the comfort of your home/work. The truth is you must make profits trading in Cryptocurrency and investing in good signals with the best guidance from Crypto Coins Trading. Start investing with Crypto Coins Trading and start earning profitable interest quick without no doubt Payout weekly 100% guaranteed profit without any Hassles, It goes on and on The higher the investment, the higher the profits. Your investment is safe and secured and payouts assured 100%. if you wish to know more about investing in Cryptocurrency and earn daily, weekly OR Monthly in trading on bitcoin or any cryptocurrency and want a successful trade without losing Contact MRS.DOROTHY JEAN INVESTMENTS profdorothyinvestments@gmail.com

    categories of investment

    Cryptocurrency

    Loan Offer

    Mining Plan

    Business Finance Plan

    Binary option Trade Plan

    Forex trade Plan

    Stocks market Trade Plan

    Return on investment (ROI) Plan

    Gold and Silver Trade Plan

    Oil and Gas Trade Plan

    Diamond Trade Plan

    Agriculture Trade Plan

    Real Estate Trade Plan

    YOURS IN SERVICE
    Mrs. Dorothy Pilkenton Jean
    Financial Advisor on Bank Instruments,
    Private Banking and Client Services
    Email Address: profdorothyinvestments@gmail.com
    Operation: We provide Financial Service Such As Bank Instrument
    From AA Rate Banks, Cash Loan,BG,SBLC,BOND,PPP,MTN,TRADING,FUNDING MONETIZING etc.

    ReplyDelete