Power Query Data Sources

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 ?

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)

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

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 !!!