Excel Get & Transform – Connect to data sources

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Moving column by drag and drop

Reordering is shown in the Applied Steps section

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

Finalize transformation by Close & Load

 The result is shown in the workbook.

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

Choosing a proper query name

Confirming the properties box after renaming

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

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

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

Refreshing via context menu

The transformation’s result is directly visible on the sheet

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

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

Selecting Advanced Editor for changing

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

Highlight Upper function code

Highlight Upper function code

Replace with Lower function code

Replace with Lower function code

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

Finish with Close & Load

Finish with Close & Load

Verify the applied steps in the worksheet

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.

Further reading ^

Book recommendation:

M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query by Ken Puls and Miguel Escobar is a nicely written "cookbook."

Video training:

"Big Data Analytics with Excel" from Guy Vaccaro contains Power Query and some more interesting stuff.

Want to write for 4sysops? We are looking for new authors.

Read 4sysops without ads by becoming a member!

1+
Share
11 Comments
  1. arley dealey 3 years ago

    The article says that Get & Transform can extract data from Active Directory but doesn't mention how and I can't see the capability to connect to AD (or any other LDAP source).

    Enlightenment, please?

    1+

    • Author

      Hi Arley,

      in short:

      Excel 2010 / 2013 with Power Query Plugin:
      Power Query (Ribbon menu) > From Other Sources > From Active Directory

      Excel 2016 / O365
      Data (Ribbon menu) > New Query > From Other Sources > From Active Directory

      in long:

      I'm preparing another post, giving some examples related to Get & Transform with Active Directory.

      1+

  2. arley dealey 3 years ago

    That's really weird. I don't get that option in my Excel 2016.

    On the "From Other Sources" submenu, my options are":
    From Table/Range
    From Web
    From Microsoft Query
    From OData Feed
    From ODBC
    From OLEDB
    Blank Query

    Nary an entry about Active Directory!

    1+

    • Author

      Guess that your version is not current. Microsoft is updating the 'Get & Transform' portion of Excel from time to time.

      My version is 16.0.6458.2025 and for me it contains:
      From Web; From SharePoint List; From OData Feed; From Hadoop File; From Active Directory; From Dynamics CRM Online and FROM Microsoft Exchange

      Please try to update to the latest version. - Hope it'll work for you.

      1+

  3. arley dealey 3 years ago

    Curiouser and curiouser...

    I finally found the Excel version & build number (used to be so easy!). Here's the tale of the tape:

    Yours: 16.0.6458.2025
    Mine: 16.0.8326.2107 (32-bit)

    So mine would appear to be slightly newer than yours. Are you running 64-bit Excel?

    1+

  4. arley dealey 3 years ago

    Aha! I may have it.

    Are you running the Office Insider builds? I have not been. I'm configuring that now and we'll see what happens.

    2+

  5. arley dealey 3 years ago

    Nope. Still stumped.

    I am now running "Version 1707 (Build 8326.2107 Click-to-Run)".

    This is part of my Office365 Business account, is installed locally and has been updated to the most recent monthly release from the Office Insiders program.

    But AD is still nowhere to be found.

    2+

  6. Zilvinas 2 years ago

    Ruben, you may be had installed some kind of Add-in, o extention?

    I see no tools to connect to AD.

    1+

  7. Author

    Hi Zilvinas,

    it's a bit of a sad story. It seems that Microsoft is only providing the AD integration if you subscribed to Office 365 (at least Pro Plus I guess).

    If you have it, please try to update to the latest build.

    I am not aware of dependencies, but I have usually the RSAT (Remote Server Administration Tools), which includes Active Directory Users & Computers installed on my machines. Perhaps there is some relation.

    Last choice could be using Excel 2010 /2013. There you can use the free plugin which contains the AD part.

    Hope it helps

    1+

  8. Edmond 6 months ago

    Hello there,

    Can you help me with the command to trigger a workbook query. I created the button and the code, I am just missing the line of code to trigger my workbook query with the name("test"). I have the code in VBA but I need the C# one.

    1+

Leave a reply

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2020

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account