Self Service BI by using Power BI – Power Query (Part 1)
In my previous posts I discussed about Power BI [link], what is it, its components, features and capabilities in the new world of Self Service IB.
Self Service BI allows end users to design & deploy their own reports, analyse within an approved & supported architecture and tools portfolios. End users do not have to worry about maintaining databases, doing integrations, creating warehouses/marts, reports, etc. The Self Service BI tool provide features which are capable enough to do all these activities in an automated, quick and efficient way, and all you have to do is learn how to configure these tools.
Power BI works ONLY with Excel & Office 365. It is nothing but a collection of different components which provide features as follows:
1. Power Query
2. Power Pivot
3. Power View (aka Crescent)
4. Power Map (aka GeoFlow)
5. Power Q & A (aka Natural Language Processing)
6. Office 365
7. Windows 8 App
–> Power Query is used to easily discover or gather data from various public or corporate sources, like:
|Web page||SQL Server database||IBM DB2 database||Windows Azure Marketplace|
|Excel or CSV file||Windows Azure SQL Database||MySQL database||Active Directory|
|XML file||HDInsight||SharePoint List|
|Text file||Access database||OData feed||SAP BusinessObjects BI Universe|
|Folder||Oracle database||Hadoop Distributed File System (HDFS)|
–> Let’s see a small demo how you can use Power Query: All you need is Excel and Power Query add-In, which you can download from Microsoft official site, [link].
Below image shows various ways you can access public or corporate data from various sources from an Excel workbook:
1. Online Search
2. From File
3. From Database
4. From Other Sources
Let’s see how Online Search works. The moment you type “Olympics 2014” it populates lot of sources from where you can fetch data from:
On selecting a source it tries to connect to the public portal and displays the Source URL while fetching the data:
And finally populates the data in tabular format in an Excel worksheet:
Go to Ribbon, click “Table Tools” -> Query -> Edit, this will open the “Query Editor” window where you can edit, clean the data. You can even Create Queries that you can save and use again later to refresh your data. Merge different tables in one step; rename, delete or even create fields. Transform your data before even importing it into a spreadsheet.
This way you can get your data ‘analysis ready’ with Power Query !!!