Excel Get & Transform – Connect to data sources

Ruben Zimmermann Fri, Sep 15 2017 office, database 11

With Get & Transform in Excel 2016, you can easily connect to various data sources such as Active Directory, SQL databases or web-based sources and then transform the data for your purposes.

Contents

Introduction
Demonstration
Further reading

Author
Recent Posts

Ruben Zimmermann

Ruben is an infrastructure specialist with strong backround in Active Directory, PKI, and System Center Operations Manager. He automates in VBS, PowerShell and C#.
You can follow him on Twitter @Ruben8Z.

Latest posts by Ruben Zimmermann (see all)

Monitoring Microsoft 365 with SCOM and the NiCE Active 365 Management Pack - Tue, Feb 7 2023
SCOM.Addons.MailIn: Monitor anything that can send email with SCOM - Mon, May 25 2020
Display a user’s logged-on computer in Active Directory Users and Computers (ADUC) - Mon, Jan 21 2019

Introduction

In 2013, Microsoft released the Power Query plugin for Excel 2010 and 2013 that enabled users to extract, transform and load the data into a workbook or a model. With its increasing popularity, Microsoft integrated the feature completely into Excel 2016/Office 365 and renamed it Get & Transform, which exactly describes its functionality. The old plugin and the corresponding Excel 2016 component are receiving regular updates.

Performed ETL (extract, transform, load) steps are recorded and stored as a query. You can alter the query through the UI or as raw text, and you can refresh, reuse and transport the query. This is possible because the query is stored in a language called M.

Extracting is done by choosing one of the various offered data sources from different kinds of text files over popular databases (not just Microsoft!), web sites (URLs), OData feeds and other services like Dynamics, Azure, SharePoint, SalesForce, Exchange Online and even Active Directory. As a fallback, ODBC is also supported.

Accessing Get and Transform in Excel

The second step transformation is done in the query editor, a window that usually directly pops up after specifying the data source parameter. From simple formatting to custom operations features all can be found here.

The Query Editor is the UI for performing all transformations

Behind the View tab, the Advanced Editor can be launched. It offers transformation directly with the M code.

I would describe M as a functional scripting language that is near the .net framework.

It is fast for processing big amounts of data and offers functions for text manipulation, dates, basic arithmetic calculations, simple conditions and even some advanced data structures like lists.

An advantage over traditional macros, which are based on Visual Basic for Applications (VBA), is that M is neither influenced by the language of the operating system nor the office package.

The third and last step load is triggered by clicking the Close & Load button. The default action transformed data is finally shown in a new Excel sheet.

After shaping the data to the desired state, it can be loaded into a workbook or a model

When handling big amounts of data, the query result can be also stored in a data model and used for further processing.

Power Query/Get & Transform is one of the new BI tools in Excel. There is Power Pivot, Power Maps and Power View.

Power Query/Get & Transform is also part of Microsoft’s Power BI. It looks and works exactly the same. M queries can be used in both without modifications.

Demonstration

The following text and pictures will briefly demonstrate Power Query/Get & Transform.

A simple text file is the data source. The file content is as follows:

After shaping the data to the desired state, it can be loaded into a workbook or a model

If you’re using Excel 2016, start by choosing the Data tab in the menu ribbon. If you have Excel 2010, you’ll find a new tab name, ‘Power Query’.

Extract

The first step is extracting the data by specifying the data source.

Navigate to the Data tab in the Ribbon menu.
Click on New Query, Point on From File, and select From Text.

Choosing from Text in the Get and Transform data source menu

Select the text file, e.g. SavingsDemo_US.txt, and confirm with Import.

Select the text file to specify the data source.
The Query Editor opens after selecting the file (or any other data source):

The Query Editor automatically performs some steps

Note: The Query Editor window is highly dynamic. By changing the size menu, items get combined and are shown in a slightly different way. A newer version also might change the UI a bit. Don’t worry if my screen looks a bit different than yours.

Looking at the right bottom, the applied steps are shown. These three have already happened.

The source step reveals the data source that was chosen.
Promoted Headers meant that the first line of the file is most likely the header and applied it.
Changed types detected the data type of each column by the best guess, but this needs to be checked, as it’s sometimes not 100% correct.

Steps can be changed or deleted in the Applied Steps section

By clicking the X on the left, the step can be deleted. By clicking on the gear-wheel on the right, the step can be amended.

The data type of the selected column is shown in the top right corner; the small arrow gives the user the chance to change it.

Detected data types should be checked and can be changed

Transform

Let’s perform some transformations to see what stands behind them.

Change the Country to UPPERCASE:

Change to the Transform tab in the ribbon menu.
Select the Country – Column.
Click on Format, and choose

Apply basic text manipulation by choosing UPPERCASE in the Format section

The text changes, and a new step is added.

Change is recorded and shown in the Applied Steps section.

As you might have noticed, the performed step is also shown as code in my window.

The formula bar shows the code to change in the M language

If you cannot see this line, change to the View tab in the ribbon menu, and check the formula bar.

Enable the formula bar in the view tab

Ordering by LastName

Select the "LastName" column, and click on the arrow.
Choose "Sort Ascending," and confirm with "OK."

Sorting LastName in Ascending order

Changed ordering shown in Applied Steps

Sorting is applied, and the new step is added to the list.

Adding custom columns (e-mail address)

Change to the Add Column tab in the ribbon menu.
Click on Add Custom Column.

Adding new column in Add Column tab.

Add "E-Mail" as the column name, and double-click

Double click available columns to create a formula

Continue the formula with typing & "." &.
Double click LastName, the formula with & "@mycompany.com."

Confirming the formula if no errors are shown

A green check mark at the bottom left indicates that no syntax errors have been detected.

Confirm the window OK.

Applied Steps shows the Added column activity

The column is visible, and the step is added.

Change the ordering of the columns

Drag the "E-Mail" column to the desired place, and drop it there.

Moving column by drag and drop

Reordering is shown in the Applied Steps section

Load

The last step is loading the data into Excel.

Move back to the Home tab of the ribbon menu.
Click on Close & Load.

Finalize transformation by Close & Load

The result is shown in the workbook.

The result is shown in the workbook

The result is shown in a new Excel sheet.

Hint

Keep a better overview; give the sheet and query a proper name.

Right-click on the query, and choose Properties …

Choosing a proper query name

Confirming the properties box after renaming

If you can’t see Workbook Queries, make it visible by changing the Data tab and clicking on "Show Queries."

Activate the Workbook queries bar if it’s not there

As a good practice, apply a naming convention, and confirm with OK.

Afterwards, rename the sheet.

The Excel sheet name and the Query should match

Save the Excel workbook.

Refreshing data - verifying the query

Now, add a new line to the text file, and save your changes.

Add more content to the text file.

Change back to Excel, and refresh the data:

Refreshing via context menu

The transformation’s result is directly visible on the sheet

All the recorded steps were performed!

Editing the query

If you need to modify the query, right-click the query on the left, and choose Edit.

Modify an existing query

The query editor comes up again, and changes can be either performed in the already-described way, or directly in the code.

Select the "View" tab, and click on Advanced Editor.

Selecting Advanced Editor for changing

Double-click Upper. Change it to Lower, and confirm with OK.

Highlight Upper function code

Replace with Lower function code

Change to the Home tab, and click on Close & Load.

Finish with Close & Load

Verify the applied steps in the worksheet

Changes are applied in the same way.

Within the Advanced Editor, the whole code can be copied onto the clipboard so the query is made transferable.

In my next post I will show you how to extract information from Active Directory with Excel Get & Transform.

Introduction