Microsoft Office 2016: The Complete Guide (2015)

GET & TRANSFORM FUNCTION IN EXCEL 2016

The Get & Transform function available in the 2016 version of Excel not only allows you to search for data sources and make connections but also lets you manipulate that data to suit your needs (such as changing data type, merging tables, or removing a column). After you have shaped your data, the results of your findings can be shared or used for reports creation.

The steps usually follow this order:

Firstly, connect – this is where you are able to access and connect to data available in the cloud, in a service, or locally.

Secondly, Transform – you can manipulate the data to suit your needs. This will not affect the original source. It will remain unchanged.

Thirdly, combine – you can generate a data model from several data sources, and get a unique view into the data.

Finally, share – after the query is completed, you can decide what to do with it, whether to save, share, or used for reports.

Each time you connect to data, transform it, or combine it with other data sources, Query Editor, one of the features of Get & Transform, records each step, and allows you to modify each step to meet your needs. You are not only able to make modifications to each step with Query Editor but are also able to undo, redo, and change the order consequently giving you the autonomy to shape your view of the connected data to suit your preference.

Get & Transform allows you to create simple or complex queries. The simplicity or complexity is determined by your needs. Each time you add steps to a query, Query Editor works in the background to create a discrete set of instructions that carry out your commands. Those instructions are created in the M Language. Advanced Editor allows users who enjoy the power and flexibility of data scripting to create M Language to manually create (or modify) queries. Whether creating steps automatically or manually, all of this power and flexibility is part of the Get & Transform collection of features in Excel 2016. A more detailed description of both Query Editor & Advanced Editor can be found further down in this article.

To begin a new query in Excel 2016’s Get & Transform function, just select the New Query button from the ribbon.

This technology is also available for the older versions of Excel with the Power Query Add-In, which is available in Power BI, as well as a download. Power Query capabilities can be seen in prior editions of Excel.

Step 1: Connection

Connection to only one data source, like Access database, or multiple data files, multiple databases, OData feeds, or Websites dispersed across the internet, by using a query is possible. Get & Transform allows you to unite all those sources by using your unique combinations, and discover new insights.

After choosing New Query from the Get & Transform ribbon selection in the Data tab, the data sources that are available are displayed in a menu. Numerous data sources are available to choose from inclusive of Azure services such as Blob Storage or HDInsight, databases such as SQL Server, Access, MySQL and Oracle, and various other sources such as SharePoint Lists, Facebook, Hadoop Files, Salesforce, the Web, and files like CVS or Excel workbooks and many more.

A Navigator window is displayed in Get & Transform when you make a connection to a data source. This allows the source data to be edited. Get & Transform launches a Query Editor once you have selected Edit from the Navigator button. Query Editor is a dedicated window that enables and displays your data and any transformations applied. Transform, which is the next section, more information about Query Editor will be provided.

Step 2: Transform

Get & Transform allows you to transform or change the data from your data sources to better help you in its analysis. Transforming data means that you are able to manipulate or change the data to suit your needs. Examples of data transformation include adding or removing a column, merging tables or changing the data type. As you transform your data, you will see it all coming together and taking on the shape you need to complete your analysis. Shaping is the term used to describe the process by which transformation is applied to one or more sets of data.

The dedicated window that Excel uses is called Query Editor which enables and displays data transformations. On the Data tab of the Get & Transform ribbon, New Query is selected. The data source is then selected (example a workbook or a database). You then choose which table or tables to be used in your query from the Navigator window which would have appeared. After a table has been selected, a preview of its data would show up in the right pane of the Navigator window.

If Load is selected, then the data source is brought directly into Excel. In such a case, Edit is selected to launch Query Editor.

Query Editor keeps a record of everything that is done with the data by labelling each step or transformation that was applied to the data. Each operation is tracked in the APPLIED STEPS section of the Query Settings pane whether the transformation is a merge, a column removal, a data connection (a data source), or a data type change.

All transformations applied to your data connections are what make up your query.

Please note that the actions defined in Query Editor will not have an effect on the original source data. It will remain unchanged. What happens instead is that Excel keeps a record of each step taken when connecting or transforming the data. After you have finished manipulating or shaping the data, a snapshot is taken of the refined data set and it is then brought into the workbook.

There are numerous transformations that can be applied to data. The M Language can be used to write your own transformations. The M Language is used by Query Editor to record background steps by using Advanced Editor. Advanced Editor is on Query Editor’s View ribbon and can be opened from there. Modifications to the M Language in relation to the existing query can also be done here. You are also able to create queries from scratch by using Advanced Editor.

After you have finished creating your query you can choose the Close & Load button from the Home ribbon tab. Your query results will be loaded into Excel and made available in a new workbook tab.

Step 3: Share

After and Excel workbook containing a query has been saved, the query is also saved automatically. If you select the Show Queries button; all queries can be viewed in a workbook in Excel. The Show Queries button is found on the Data tab on the Get & Transform ribbon.

All queries in a workbook will be shown in the Workbook Queries pane.

The possibilities are not limited. Your queries can be shared with just about anyone within your company or organization by using Power BI Data Catalog. You can also create a query that will be used on a regular basis and use it in several workbooks to save yourself time and effort. Data Catalog exists to make your life easier. It takes away the headaches of saving and emailing workbooks from Excel and minimizes the number of workbook versions awaiting you in your email inbox. Who wants to always be trying to figure out which is the original version, or which is out of date, or what changes have been made? On the Workbook Queries pane, there is a menu which offers many options, one of which is the Send to Data Catalog. Just make a right-click on the query to access the option that best suits your needs.

Please make note of the other options in the right-click menu as well. A Duplicate of a query can be made. This allows you to change some or all elements of a query while the original query remains unchanged. This is like creating a query template which can then be modified and customized to create datasets for specific purposes. Examples are dataset for inventory, dataset for retail, or dataset for wholesale. They are all based on the same data connections.

Queries can also be Merged or Appended. This allows you to turn your queries in building blocks that can be used over and over again.

Your workbook can be published to Power BI. Online reports can be shared with your group, automatically refreshed, and refined. The following simple steps allow you to publish a workbook to Power BI: