Get & Transform (formerly known as Power Query) is perhaps the biggest hidden secret in Excel and the tool that can save you hours and hours of boring and repetitive work. To analyse data in Excel, in many cases you must first import it. This can be done manually or use VBA, but manually importing data into Excel is slow, tedious and error-prone and VBA requires some programming knowledge.

Get & Transform allows the automation of the process of importing data, but it requires no coding knowledge and tends to result in better performance. But it doesn’t only import data; Excel Get & Transform is an advanced ETL tool.  ETL is an acronym for Extract, Transform and Load.

It allows you to:

• Extract: Most data that is analysed in Excel is imported from an external data source. Extract means moving this data from the external data source into the Get & Transform tool. This can include Excel files, Text or CSV files, Databases such as Microsoft SQL Server, Microsoft Access and Microsoft Exchange, Facebook and even web pages.

• Transform: Extracted data often is often in a form that isn’t easily analysed. There may be unwanted columns, bad formatting or corrupted data. Get & Transform has a vast array of features that enable you to clean your data before loading to Excel table. The extracted data can be transformed in almost unlimited ways:

• • Removing columns
  • Filtering the data
  • Grouping the data,
  • Pivoting/unpivoting the data
  • Splitting up text
  • Extracting keywords from text
  • Add rows from another table
  • Remove Duplicate Records
  • Split a Column the Number of Chars
  • Duplicate Columns
  • Use First Row as Headers
  • Remove Columns
  • Remove Duplicates
  • Fill Down Values
  • Create Index Columns
  • Remove Rows With Errors
  • Group Rows and Get Counts
  • Reverse Rows
  • Transpose
  • Replace Values
  • Split First & Last Name
  • Consolidate Excel Workbooks or Worksheets

• Load: The transformed data from the Get & Transform tool is loaded into an Excel table or into an Excel Pivot Table, where it can be analysed and shared.

Whatever you have done, the whole transformation of the data process can then be saved as a Query. Perhaps the greatest and most time-saving feature of Get & Transform is that it allows you to set up a query once and then reuse it with a simple refresh. Get & Transform is very easy to use and pick up, more so than other Excel tools like formulas or VBA. And no coding knowledge is required. Get & Transform records all your transformations step by step. You can even go back and amend any of the steps individually.

Get & Transform is part of Infero’s new Data Analysis and Visualisation with Microsoft Excel Course. Contact us to learn more about it. The applications for your own work in Excel could be endless.