7 Easy ways of connecting to data in Power BI

7 Easy ways of connecting to data in Power BI

The first step in creating stunning data visualizations begins with connecting to your data sources. In the Microsoft Power BI Desktop app, you can connect to nearly 100 out-of-the-box data connectors including CSV, Excel, SQL Server, SharePoint, Dynamics 365, and Salesforce just to name a few.

Power BI lets you connect to your data in several different ways, allowing you to choose the one that makes the most sense for your workflow.

In this blog, we will explore 7 easy ways to connect to your data from the Power BI Desktop app (and one more advanced way). These include:

Following are detailed instructions for connecting to your data in Power BI Desktop using each of the methods mentioned above. Oh, and if you are a code whiz, read on for a bonus method of connecting to data in a more programmatic way.

1.0 Connect to Data from Opening Screen

When you open the Power BI Desktop, the app hits you with two options to get data into Power BI in a ‘pop-up’ opening screen. Note that if you have previously unchecked the Show this screen on startup box, you will not see the opening screen and should use the other ways to get access to your data sources.

1.1 Connect to Data from Opening Screen

If you are looking to connect to a data source that you have never connected to or data you have not connected to in a while, you can select Get data from the opening screen.

When you click Get data, the Get Data dialogue box will open where you can choose which data source you would like to connect to.

1.2 Get Recent Data from Opening Screen

From the opening screen you can also access data sources you have used recently. To access data used recently, select Recent sources from the opening screen.

Clicking the Recent sources option will open the Recent Sources dialogue window which allows you to choose which data source you want to connect to.

2.0 Connect to Data from the Home Tab

If you are like me and prefers to work in the Power BI Desktop app instead of the opening screen, or if you unchecked the Show this screen on startup, there are other options to connect to your data. On the Home tab, there is a Data section that you can use to connect to your data sources.

2.1 Get Common Data Sources from the Home Tab

In the Data section, Microsoft provides a few common data sources to choose from. These include Excel, Power BI Datasets, SQL Server, the option to enter your own data, and more recently Microsoft Dataverse. Select one of these options to connect to your data source.

2.2 Get Any Data Source from the Home Tab

You can also use the options in the Data section in the Home tab to connect to any data source by clicking on Get data. This will open the Get Data dialogue box.

Clicking the down arrow on the Get data button opens a menu with common data sources. From this menu, you can select a commonly used data source or click More to open the full Get Data dialogue.

2.3 Get Recent Data from the Home Tab

If you are looking for a dataset you used recently, you can access recent data sources from the Data section in the Home tab. To do this, click the Recent sources button on the Data section to open the Recent Sources dialogue.

3.0 Connect to Data from the Power Query Editor

The remaining options to connect to data in Power BI are through the use of the Power Query Editor. These methods are more commonly used if you wish to connect to multiple data sources and make transformations to your data or if you are looking to use SQL to query your data source prior to bringing it to Power BI.

To open the Power Query Editor, navigate to the Queries section on the Home tab and click Transform data. When you open the Power Query Editor, there is a New Query section where you can connect to your data sources or enter your own data.

3.1 Get Any Data Source from the Power Query Editor

To connect to any data source using the Power Query Editor, you can click New Source to open the Get Data dialogue.

Alternatively, you can also click the down arrow on the New Source button to expand a menu of common data sources. From this menu, you can also click More to expand the complete Get Data dialogue.

3.2 Get Recent Data Sources from the Power Query Editor

Like the Home tab in the Power BI Desktop app, the Power Query Editor also offers the option to access recently used data sources. You can do this by clicking on Recent Sources to expand the menu.

[BONUS] Get Any Data Source Using the Advanced Editor in Power Query Editor

Finally, there is a more advanced way to connect to your data sources from the Power Query Editor and this is using the Advanced Editor. This method is useful for the Power BI users who prefer a more programmatic way to connecting to and cleaning data. Moreover, this method is also handy for writing SQL queries against your databases to minimize your model size and improve report performance.

To connect to your data sources via the Advanced Editor, you first need to create a Blank Query.

Once you have a blank query, navigate to the Query section of the Home tab in the Power Query Editor and select Advanced Editor.

When you click Advanced Editor, the editor window will open for the blank query you just created. In this window you can now use M language to write your code that will connect to your data source, query your data, and make any transformations prior to loading it to the data model.

Need Help Getting Started With Power BI?

Our Microsoft Certified consultants can help with the implementation of Power BI in your organization

Leave a Reply