Excel as a source

Believe it or not, Excel continues to be the most popular application in the world and as such, you should expect that at some point you will be using it as a data source:

  1. To get started, open the Power BI Desktop and close the start-up screen if it automatically appears.
  1. Under the Home ribbon, you will find that Get Data button, which you already learned is used for selecting and configuring data sources. Selecting the down arrow next to the button will show you the most common connectors, but selecting the center of the button will launch the full list of all available connectors. Regardless of which way you select the button, you will find Excel at the top of both lists.
  2. Navigate to and open the file called AdventureWorksDW.xlsx from the book resources. This will launch the Navigator dialog, which is used for selecting the objects in the Excel workbook you desire to take data from:

  1. In this example, you see six separate spreadsheets you can choose from. Clicking once on the spreadsheet name will give you a preview of the data it stores, while clicking the checkbox next to the name will include it as part of the data import. For this example, select the checkboxes next to all of the available objects, then notice the options available in the bottom right.
  2. Selecting Load will immediately take the data from the selected spreadsheets and import them as separate tables in your Power BI data model. Choosing Edit will launch an entirely new window called the Power Query Editor that allows you to apply business rules or transforms to your prior to importing it. You will learn much more about the Power Query Editor in Chapter 2, Data Transformation Strategies. Since you will learn more about this later, simply select Load to end this example.

Another topic you will learn more about in Chapter 6,  Using a Cloud Deployment with the Power BI Service, is the concept of data refreshes. This is important because, when you import data into Power BI, that data remains static until another refresh is initiated. This refresh can either be initiated manually or set on a schedule. This also requires the installation of a Data Gateway, the application in charge of securely pushing data into the Power BI Service. Feel free to skip to Chapter 6,  Using a Cloud Deployment with the Power BI Service, if configuring a data refresh is a subject you need to know now.