MS Power Query is a very powerful business intelligence tool by Microsoft. As an Excel addin it can, together with Power Pivot, replace tools which were made only for experienced developers (SQL Server Integration Services etc.) The biggest advantage of this tool is the possibility to use a wide variety of connectors to data sources.
User can easily (and without programming knowledge) connect to these data sources and edit them in many ways. Power query nicely replaces so-called ETL processes (Extract, transfer, load) – data preparation for reporting.
What Can Power Query Do?
- Loads data (Extract) – from any given data source as a relational database or common file of different types
- Edits data (Transfer) – operations as merging tables, connecting tables, clean or add, edit or delete column. All of it can be done without any problem. You can prepare your data for reporting.
- Loads data to Excel (Load) – edited data are at the end-loaded into Excel where it can be analyzed, added to chart etc.
Great advantage of power query is the ability to remember all changes done by the user. Hence you can replicate them anytime by updating Excel. This function means big savings in automation. Reports and data are often needed repeatedly and this function eliminates repeated processing out of the game. All you need to do is refresh Excel file.
Everything mentioned above can be done through simple guide. No programming skills needed – just let yourself be guided.
Which Data Can I Connect to via MS Power Query?
There is a vast amount of data sources which can we work with. Power query functions are available automatically since Excel 2016 using option Power query
- 1 – Load data from web
- 2 – Load data from file
- a – Excel
- b – CSV
- c – XML
- d – Text
- e – Folder
- 2 – Load data from database
- a – MS SQL Server database
- b – MS Access
- c – SQL Server Analysis Services database
- d – Oracle database
- e – IBM DB2 database
- f – MySQL database
- g – Postgress SQL database
- h – Sybase database
- i – TeraData database
- 3 – Load data from other sources
- a – MS Sharepoint
- b – Odata Feed
- c – Hadoop (HDFS)
- d – AD (Active Directory)
- e – MS Exchange
- f – Microsoft Dynamics CRM
- g – Facebook
- h – SAP Business Objects BI Universe
- i – SalesForce Objects
- j – ODBC
- k – Blank query
Zdroj: Solutiondesign.com