Saturday, January 27, 2018

What is Power Query ?

Power Query is an ETL tool, that can be used to extract, transform and refine data.

Here ETL stands for Extract, Transform & Load.

              E :- It can extract data from 30+ data sources and convert into a consolidated data format  for processing/transformation

              T :- This step process the data like cleaning, Splitting a column into two or multiple column, find and replacing, appending and merging two data tables, aggregating & summarising data, pivot or un-pivoting columns, transposing rows and columns etc.

            L :- And finally it loads the data to an excel table or to a data model or simple creates a connection (The connection can be used to create Pivot).

Some facts about Power Query

  • It is Microsoft excel based free add-in. Download.
  • It is backward compatibe. The Add-in available for excel 2010 & 2013. It is integrated into excel in MS Excel 2016 and know as Get & Transform.
  • It is available for both 32 & 64 bit excel.
  • Learning & mastering power query is much faster than learning Excel formulas and VBA.
  • It can be used to automate some task.
The official language for power query is M. And there is no need to learn M. What ever we do in the PQ editor it will automatically record the steps in M language.

Who should learn/use Power Query?

  • Those who spend much of their time in combining data from multiple files
  • Those who work in large volumes of data.
  • Those who work in data from multiple sources.