Power BI 2: Connecting Data Sources to Power BI
How many data sources, Flat Files, SQL Tables, Sharepoint, manual table, and create a table with DAX
Thanks to BowTied_Analyst for helping to write this.
This article will show you how to get data into Power BI. This is so that you can create some of the interactive visualizations typical of the software. First some basic vocabulary. Power BI connects to data sources. Once a data source is connected to Power BI, data are stored in tables. Tables are either based off of different data sources or created by an analyst in Power BI.
How many data sources can Power BI connect to?
Power BI can connect to hundreds of types of data sources. While you can connect to all of these, 95% of the connections I make as a data analyst are local flat files (excel or CSV), relational databases or flat files located on a SharePoint. If you're doing independent, non-professional or quasi-non-professional BI work, connecting to these sources is all you really need. Knowing the minutiae of connecting to some random, seldom-used data source will not be a difference-maker for getting a data analyst job.
If you're pursuing Power BI for personal project, you can probably figure out how to connect to your data source of choice. Much more skill is needed in using Power Query to clean your data, build a data model, create Dax equations or designing your actual finished report.
Connecting Power BI to a flat file
A flat file is just another name for an Excel or CSV file. In this section you’ll learn how to connect to flat files located locally on your computer or on a network drive. When you have a new Power BI report open and are faced with a blank screen, Power BI makes it incredibly clear what and how to add excel data. From your home screen, there are three places you can click to add data from an excel document, they are:
Get Data
Excel Workbook
Import Data From Excel
From there you select your excel file and a navigation window will open. Then, select a tab in your excel file.
Once selected you will get a preview of the data source and how it will be brought into Power BI as a table. You will have an option to either load or transform the data. Data transformation in Power BI uses power query to execute steps. These steps change the format of your data table and make it easier to work with.
How to transform data will be the subject of a later article. For now, you can just load the data and you’re done! Pretty easy.
How to Connect to SQL Tables
Connecting to a relational database is almost the same as connecting to a local excel or csv document. From your home screen there are two places you can connect which are:
Get Data
Import Data from SQL Server
From there you will need to add your server. You have the option of calling
Keep reading with a 7-day free trial
Subscribe to Data Science & Machine Learning 101 to keep reading this post and get 7 days of free access to the full post archives.