Blogie119

Merge queries (Power Query) Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 When you merge, you typically join two queries that are either within Excel or from an external data source. In addition, the Merge feature has an intuitive user interface to. I just bought Office 365 and I don't see Power Query on the menu or a means to download the Addin lick in Excel 2016. Microsoft Office Technician: Rennan Lui, Computer Support Specialist replied 10 hours ago.

Power Query is a data transformation and data preparation engine. Power Query comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations. Because the engine is available in many products and services, the destination where the data will be stored depends on where Power Query was used. Use Power Query in Excel for Mac Excel for Microsoft 365 for Mac Excel for Mac now supports Power Query refresh for many data sources, as well as query creation through VBA. Authoring in the Power Query Editor is not supported yet. You need not install Power Query for Office 365. This is included by default in 2016/365. The name of Power Query has been changed to Get and Transform Data in 2016 / 365. Data tab You will have Get & Transform Group (This is what Power Query is).

November 11, 2020 - by Bill Jelen

Power Query is built in to Windows versions of Office 365, Excel 2016, Excel 2019 and is available as a free download in Windows versions of Excel 2010 and Excel 2013. The tool is designed to extract, transform, and load data into Excel from a variety of sources. The best part: Power Query remembers your steps and will play them back when you want to refresh the data. This means you can clean data on Day 1 in 80% of the normal time, and you can clean data on Days 2 through 400 by simply clicking Refresh.

I say this about a lot of new Excel features, but this really is the best feature to hit Excel in 20 years.

I tell a story in my live seminars about how Power Query was invented as a crutch for SQL Server Analysis Services customers who were forced to use Excel in order to access Power Pivot. But Power Query kept getting better, and every person using Excel should be taking the time to learn Power Query.

Get Power Query

You may already have Power Query. It is in the Get & Transform group on the Data tab.

But if you are in Excel 2010 or Excel 2013, go to the Internet and search for Download Power Query. Your Power Query commands will appear on a dedicated Power Query tab in the Ribbon.

Power Query Office 365 Install

Clean Data the First Time in Power Query

To give you an example of some of the awesomeness of Power Query, say that you get the file shown below every day. Column A is not filled in. Quarters are going across instead of down the page.

To start, save that workbook to your hard drive. Put it in a predictable place with a name that you will use for that file every day.

In Excel, select Get Data, From File, From Workbook.

Browse to the workbook. In the Preview pane, click on Sheet1. Instead of clicking Load, click Edit. You now see the workbook in a slightly different grid—the Power Query grid.

Now you need to fix all the blank cells in column A. If you were to do this in the Excel user interface, the unwieldy command sequence is Home, Find & Select, Go To Special, Blanks, Equals, Up Arrow, Ctrl+Enter.

In Power Query, select Transform, Fill, Down.

All of the null values are replaced with the value from above. With Power Query, it takes three clicks instead of seven.

Next problem: The quarters are going across instead of down. In Excel, you can fix this with a Multiple Consolidation Range pivot table. This requires 12 steps and 23+ clicks.

In Power Query select the two columns that are not quarters. Open the Unpivot Columns dropdown on the Transform tab and choose Unpivot Other Columns, as shown below.

Right-click on the newly created Attribute column and rename it Quarter instead of Attribute. Twenty-plus clicks in Excel becomes five clicks in Power Query.

Now, to be fair, not every cleaning step is shorter in Power Query than in Excel. Removing a column still means right-clicking a column and choosing Remove Column. But to be honest, the story here is not about the time savings on Day 1.

Power Query Office 365 Download

But Wait: Power Query Remembers All of Your Steps

Look on the right side of the Power Query window. There is a list called Applied Steps. It is an instant audit trail of all of your steps. Click any gear icon to change your choices in that step and have the changes cascade through the future steps. Click on any step for a view of how the data looked before that step.

When you are done cleaning the data, click Close & Load as shown below.

Tip

If your data is more than 1,048,576 rows, you can use the Close & Load dropdown to load the data directly to the Power Pivot Data Model, which can accommodate 995 million rows if you have enough memory installed on the machine.

In a few seconds, your transformed data appears in Excel. Awesome.

The Payoff: Clean Data Tomorrow With One Click

But again, the Power Query story is not about the time savings on Day 1. When you select the data returned by Power Query, a Queries & Connections panel appears on the right side of Excel, and on it is a Refresh button. (We need an Edit button here, but because there isn't one, you have to right-click the original query to view or make changes to the original query).

It is fun to clean data on Day 1. I love doing something new. But when my manager sees the resulting report and says “Beautiful. Can you do this every day?” I quickly grow to hate the tedium of cleaning the same data set every day.

So, to demonstrate Day 400 of cleaning the data, I have completely changed the original file. New products, new customers, smaller numbers, more rows, as shown below. I save this new version of the file in the same path and with the same filename as the original file.

If I open the query workbook and click Refresh, in a few seconds, Power Query reports 92 rows instead of 68 rows.

Cleaning the data on Day 2, Day 3, Day, 4,...Day 400,...Day Infinity now takes two clicks.

This one example only scratches the surface of Power Query. If you spend two hours with the book, M is for (Data) Monkey by Ken Puls and Miguel Escobar, you will learn about other features, such as these:

• Combining all Excel or CSV files from a folder into a single Excel grid
• Converting a cell with Apple;Banana;Cherry;Dill;Eggplant to five rows in Excel
• Doing a VLOOKUP to a lookup workbook as you are bringing data into Power Query
• Making a single query into a function that can be applied to every row in Excel

For a complete description of Power Query, check out M Is for (Data) Monkey by Ken Puls and Miguel Escobar. By late 2019, the retitled second edition, Master Your Data, will be available.

Thanks to Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser, and Colin Michael for nominating Power Query.

Title Photo: pan xiaozhen at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.

Power Query Office 365 Proplus

The CData ODBC driver for Office 365 uses the standard ODBC interface to link Office 365 data with applications like Microsoft Access and Excel. Follow the steps below to use Microsoft Query to import Office 365 data into a spreadsheet and provide values to a parameterized query from cells in a spreadsheet.

If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.

Office 365 uses the OAuth authentication standard. To authenticate requests, you will need to obtain the OAuthClientId, OAuthClientSecret, and OAuthCallbackURL by registering an app with Office 365. See the 'Getting Started' chapter of the help documentation for a guide to using OAuth.

Install Power Query Office 365

You can then work with live Office 365 data in Excel.

Power Query Office 365 Missing

1. In Excel, open the Data tab and choose From Other Sources -> From Microsoft Query.
2. Choose the Office365 DSN. Select the option to use Query Wizard to create/edit queries.
3. In the Query Wizard, expand the node for the table you would like to import into your spreadsheet. Select the columns you want to import and click the arrow to add them to your query. Alternatively, select the table name to add all columns for that table.
4. The Filter Data page allows you to specify criteria. For example, you can limit results by setting a date range.
5. If you want to use parameters in your query, select the option to edit the query in Microsoft Query.

6. To set a parameter in the query, you will need to modify the SQL statement directly. To do this, click the SQL button in the Query Editor. If you set filter criteria earlier, you should have a WHERE clause already in the query.

   To use a parameter, use a '?' character as the wildcard character for a field's value in the WHERE clause. For example, if you are importing the Files, you can set 'UserId=?'.

7. Close the SQL dialog when you are finished editing the SQL statement. You will be prompted to enter a parameter value. In the next step, you will select a cell to provide this value. So, leave the box in the dialog blank.

8. Close Microsoft Query. The Import Data dialog is displayed. Enter a cell where results should be imported.

9. Close the Import Data dialog. You will be prompted to enter a parameter value. Click the button next to the parameter box to select a cell. Select the option to automatically refresh the spreadsheet when the value changes.

Power Query For Office 365