Power Query – Powerful Tool For Self Service BI – Introduction

Last modified date:

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?

  1. Loads data (Extract) – from any given data source as a relational database or common file of different types
  2. 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.
  3. 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

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Don't Miss Out Power Query

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.