In recent months, there has been a big push for self service business intelligence tools by Microsoft. The center of this universe is Microsoft Excel which is a common application used by the data analyst.
Today, I will continue talking about Power Query which is a self service ETL tool. This tool was released in August of 2013. In addition to my blog, the online help is a good place to start learning about this tool.
Last time, I showed you how to find an online data source, extract the data, perform some simple translations and load the data into MS Excel.
Now I am going to perform a high level review of all the different import options that are available. Power Query is very flexible given the larger number of import options available to the end user.
The “get external data” and “excel data” sections of the ribbon is where we will concentrate.
The “online search” and “from web” ribbon buttons go hand in hand. The search was used last time to identify a public web data source for loading. However, it we knew the web URL, we could have used the second option.
It is amazing that many companies still use flat files to exchange data. The “from file” ribbon button allows you to import the following data files: Excel, Comma Separated Values (CSV), Extensible Markup Language (XML), Text File, and From Folder.
Larger and more mature companies store their data in a relational database. That is why the “from database” ribbon button supports the following databases: SQL Server, Azure SQL, MS Access, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase and Teradata. These import options cover the majority of relational databases used day-to-day.
Cloud based providers and on-line social media are having an impact on our lives and how we do business. The “from other sources” ribbon button allows the BI technologist to access these data sources.
The following data sources can be imported: SharePoint List, OData Feed, Azure Marketplace, Hadoop File, Azure HDInsight, Azure Blob Storage, Azure Table Storage, Active Directory, MS Exchange, and Facebook.
In short, I can see many future articles exploring all these continually growing data source types.
Last but not least, there maybe times in which you have internal data that you want to add your data analysis. For instance, a part number to part name translation from a vendor that does not supply this information in a electronic format. How do you get this information into the data model?
Do not sweat! The “from table” ribbon button under the “excel data” section allows you to import any range of data from an existing worksheet into your model. Just supply the cell range for your table and whether or not the table has headers.
To recap, the Power Query tool allows a BI technologist to import data “from web”, “from files”, “from database”, “from other sources” and “from tables” — hand crafted MS excel workbooks. It is a very flexible tool to import (extract from source) your data into excel for analysis.
Next time, I will be talking about the Query Editor. This is where custom transformations are applied to the raw data to mold it into a form that you want.