Excel to BI

Power Query Data Sources

2018-01-30T20:42:00.000+05:30

Power query supports 30+ data sources from where we can import/extract data. Below are the list of data sources.

Standard Connectors

Text or CSV file
Web page
Excel Table/Range

Import Data from a File

Excel workbook
Text or CSV file
XML file
JSON file
Folder

Import Data from a Database

SQL Server database
Access database
SQL Server Analysis Services Database (Import)
Oracle database
IBM DB2 database
MySQL database
PostgreSQL database
Sybase IQ database
Teradata database

Import Data from Microsoft Azure

Azure SQL database
Azure HDInsight (HDFS)
Azure Blob Storage
Azure Table Storage
Import Data from Online Services
Microsoft Exchange Online
SharePoint Online List
Microsoft Dynamics 365 (online)
Facebook
Salesforce Objects
Salesforce Reports

Import Data from Other Sources

Sharepoint List
OData feed
Hadoop File (HDFS)
Active Directory
Microsoft Exchange
ODBC source
OLEDB source
Create a Blank Query

What is Power Query ?

2018-01-27T20:36:00.000+05:30

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.

What is Business Intelligence (BI)

2018-01-22T20:50:00.000+05:30

A tool (or a set tools) that can be used to find patterns or actionable insights from data ( or from large volume of data). The tool extracts, analyses, discovers and transforms the data into insights and present the insights to user in report or dashboard format. The reports & dashboard can be a combination of both table and or charts.

There are dozens of vendors offering BI tools (for example Tableau, Power BI, Qlik etc).

Though, as per Gartner Magic Quadrant report, Tableau & Power BI are at the same level. But Personally I prefer Power BI over Tableau.

What are the benefits of Business Intelligence (BI)

The BI tool provides the hidden insights from data. And the user can use these insights to make better & faster decisions, increase operational efficiency, identify the market trend.

Welcome to my blog

2018-01-18T21:08:00.000+05:30

Hi,

Welcome to my blog ExcelToBI.com

My name is Sadasiv Padhiary. I have created this blog for those who are working in Excel (MS Excel) and want to move from excel based report to BI (Business Intelligence) report. Here BI means I am referring to POWER BI.

To be true I am also using Microsoft Excel since last 10 year. And now exploring and experimenting with BI (actually trying to use in my day to day job). So i think this is the best time to share my day to day experience.

Here I will mostly share about Microsoft BI Product like Power Query, Power Pivot (DAX) and Power BI. And for excel related article, I am going to create another blog site XLGuru.Net

Happy Learning !!!