Friday, July 8, 2016

Get & Transform

As we have discussed in years past, pulling data from sources outside of Excel is a fact-of-life for many Excel users.  Microsoft Query used to be the Go-To add-in for importing data into Excel, but it had several limitations.  Microsoft then introduced the considerably more robust Power Query, which provided a more intuitive user experience for uncovering, combining, and refining data across a wide variety of outside sources.

Microsoft Excel continues to evolve, of course, and what was previously referred to as Power Query is now known as the even more powerful Get & Transform tool in Excel 2016.

Get & Transform includes a powerful new set of features which provides fast, easy data gathering and shaping capabilities. It enables you to connect, combine, and refine data sources to meet nearly any of your analysis needs. These features are also used in Power BI, and in Power Query that were available for previous versions of Excel such as 2010 and 2013.

To access the power of Get & Transform in Excel 2016, just create a query in your workbook. A query, of course, enables you to connect to and transform data from a wide variety of available data sources. Loading the transformed data into a table, or into the built-in Data Model in Excel 2016 is a snap, and the data will even refresh when it is needed!

To create a query in Excel 2016, use the Data tab in the ribbon, then select the New Query button from the Get & Transform ribbon group.  Some of the data sources include:
  •   Text file
  • Web page
  •  Facebook
  • Wikipedia
  • Sybase Database
  • Teradata Database
  • SharePoint List
  • OData feed
  •  CSV file SQL database
  •  Microsoft Exchange
  • Access database
  • Oracle database
Get & Transform.  Another way to harness the Power of Excel!

No comments: