3. Connecting with data

Power BI can connect and load data from wide range of data sources. The data modeling capabilities of Power BI are not restricted to the type of data source.

To check the available data sources in Power BI, launch Power BI desktop and select Get data from the Home tab     

Connecting to Data in Power BI using Get Data

Get Data will display the list of commonly used data sources. Navigate all the way down in the list and click on More, it will show up different data sources

Connecting and Loading Orders.xlsx

In this section, we will load Orders table which is in xlsx format.

This table in enclosed with this tutorial. Download Orders.xlsx in your c:\ or location of your choice.

This table contains, details of an order, such as, Order ID, Order date, Shipment details, Sales, Profit, Quantity, Discounts amounts.

Steps:

1. Launch Power BI desktop and Cancel the welcome screen.
2. Save your application. In Power BI desktop, navigate to menu File and select Save As.
3. From the Home tab, select Get Data and then select Excel.

• Navigate to the downloaded Orders.xlsx file on your laptop.
• On the Navigator dialogue, select the sheet name Orders. It will display the preview of the data.

• Select Load.
• This will load the Orders table. You can verify by navigating to the Data view. It will display the table and fields loaded.

• You can also navigate to Model view and it will display table/s in your data model. Right now you have only one table loaded.

Connecting and Loading Products.xlsx

In this section, we will load Products table which is in xlsx format.

This table in enclosed with this tutorial. Download Products.xlsx in your c:\ or location of your choice.

This table contains details of the Products.

Steps:

1. Follow the steps as described in the previous section when we loaded Orders.xlsx.
2. After you have successfully loaded Products table, navigate to Model view, you will see two tables, Orders and Products.

Relationship between the Tables

You will notice that Power BI has automatically deducted relationship between these tables.

To explore this relationship, right-click on the relationship connector and select properties

In the next Edit Relationship screen, you will see that Power BI has created/deducted a relationship based on RowID column of the two tables.

This is incorrect as we want the relationship to occur based on the Product ID column.

To get the correct relationship, select Product ID column in both the tables.

This will fix the relationship between the two tables. Based on the data in tables, Power BI suggest a Many to Many relationship.

If you feel this is incorrect, you can always change it.

 Creating new Relationships

In the previous section, we have fixed an auto-detected relationship. In case, Power BI fails to auto-detect relationship between two tables, you can create a new relationship.

To create a new relationship, navigate to Data view, and from Table Tools tab in the ribbon, select Manage relationships.

In the Manage relationships screen, select New (as shown below)

Right now, we have loaded only two tables. But if you have more tables and you want to create relationships between them, you can do so using Manage relationships.