Power Query provides a set of capabilities around data acquisition and transformation in Excel. In combination with Power BI for Office 365, Power Query helps users share queries within their organization and easily find queries shared by others using Online Search. Excel 2016 Onward and Office 365. In Excel 2016 onward and Office 365 Power Query is integrated and is included in the Data tab, which means you don’t need to download or install it. You’ll also find that in Excel 2016 Power Query took on a new name: Get and Transform (personally I preferred Power Query, but. Build powerful end-to-end business solutions by connecting Power Automate across the entire Microsoft Power Platform—and to Microsoft 365, Dynamics 365, Azure, and hundreds of other apps—to drive innovation across your entire organization. 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).
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.
You can then work with live Office 365 data in Excel.
In Excel, open the Data tab and choose From Other Sources -> From Microsoft Query.
Choose the Office365 DSN. Select the option to use Query Wizard to create/edit queries.
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.
The Filter Data page allows you to specify criteria. For example, you can limit results by setting a date range.
If you want to use parameters in your query, select the option to edit the query in Microsoft Query.
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=?'.
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.
Close Microsoft Query. The Import Data dialog is displayed. Enter a cell where results should be imported.
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.
The data is now imported into Excel. When you change the value in cell B1, the data will be filtered by the specified search criteria.
One of the interesting additions to the Power Query Preview December update is the ability to import data from Microsoft Exchange. In this blog post, Miguel Llopis (a Program Manager in the Power Query team) will walk us through the capabilities exposed by this new feature and show how you can easily do analytics on top of your Exchange account.
Connecting to Exchange using Power Query
If you have installed the latest Power Query Preview update you will be able to connect to Microsoft Exchange via the “From Other Sources” menu in the Power Query ribbon.
After selecting this option, you will be prompted for your Exchange account credentials. Once you provide credentials the Navigator side pane will show the list of items available in your Exchange account, including Mail, Calendar, People, Tasks and Meeting Requests. You can use the Navigator to preview and select one or more items from this list. The Navigator will then let you load these items directly into the workbook or apply some filters and transformations before loading.
In this case, we will select “Mail” and click “Edit”, so we can apply filters and transformations to our query before downloading the Mail data from Exchange. The Query Editor will show us a preview of the Mail items in my account, where we can apply any table transformations available in Power Query just as we do with data coming from other sources like databases or files.
Analyzing emails sent directly “to me” per day
To showcase some of the possible analytics that we could build, we will create a query that returns the number of emails sent directly to me by day.
You can see how the first column in the table contains the Folder Path information for each Mail item. In this case, my Exchange account has a set of rules and it sends the items that contain my email address in the “To” line to a certain folder (“ToMe”). We can filter this column to only include items in this folder, by using the Filter menu in the column header. You will notice that, because I have too many items in the Inbox folder, the filter values list only displays “Inbox” initially, since the Query Editor only uses a sample of the data for preview and transformation purposes, but there is a way for us to load up to the top 1,000 distinct values in this column by using the “Load more” link at the bottom of the filter menu.
After loading more items, I can search for the “ToMe” folder and apply the desired filter.
Note that even if you don’t have custom rules and a folder structure to organize your mail, you can also achieve the goal of filtering down to only include emails with a certain recipient in the To line by expanding the “ToRecipients” column in this table and filtering by email address.
Now that we have filtered down to only the items that we want to analyze, let’s apply some extra transformations so we are able to group these items by received date. Notice that the table contains a “DateTimeReceived” column that we can use. In this case, since we want to group just by received date and not date/time, we will need to change the type of the “DateTimeReceived” column to Date. In order to do that, we can select the column and use the Data Type menu in the ribbon to set the column type to Date.
Let’s also rename this column to “Date Received”.
To simplify this table and filter out unnecessary columns for our analysis, we can remove all columns in the table except for the “Subject” and “Date Received” columns, and change the Load Settings at the bottom of the Query Settings pane so we load the resulting data directly into the Data Model and not to the worksheet.
Power Query Tutorial
Now we can click “Apply and Close” and the query will start evaluating. This evaluation may take some time, as in my case it will download over 35,000 rows of Exchange mail data.
Creating a Power View report
Once the query has finished downloading, we can use Power View to build a line chart that displays the count of items per date.
It doesn’t come as a surprise, but we can see how the volume of my incoming emails per day during the last year decreased during the holidays, such as Christmas and New Year, or during some other periods when I took time off work.
Power Query Office 365 Online
Sharing this query with others
Power Query Office 365 Excel
In addition to visualizing this report, I could also share the query with other users using the Power BI for Office 365 Preview. I can share the query with everyone in my organization, but given that I don’t want to include a preview of the query (which contains email subjects), I can disable the preview as part of my sharing settings.
Download Power Query
After sharing the query, it will be easily discoverable for other users in my organization via Online Search, and they will be able to run the same analysis using their own Exchange account credentials.