Power BI allows you to connect with various data sources such as worksheets, databased present in cloud services, local databases, Azure, Social Media databases, and more. On some occasions, the data you have collected from the data sources is not well structured and managed as you would have thought. In order to structure the data in the required format, you have to transform it by splitting columns, changing the data types, renaming some columns, creating meaningful relationships among columns, etc.

The examples shared in this chapter have been taken from the Microsoft Access database. You can download it from . Once you have downloaded it, you need to import the same into Power BI Desktop by navigating to “Get Data” -> Database -> Access Database.

Power BI supports around 60 connectors for cloud services like Marketo and GitHub. Connecting to generic sources is very easy via CSV, XML, ODBC, and text format. You can even extract all the tabular data easily from any website.

In order to connect to data in Power BI, you need to follow the below steps:

1. Open Power BI Desktop using shortcut link on your desktop or from the Startup Menu. Select the “Get Data” option present in the Home tab ribbon, as shown in the below image.

2. You will find various data sources to choose from. You can choose from any of these like Excel, Power BI datasets, SQL Server, Text/CSV, OData feed, Web, Analysis Services, and More. Select the required one in order to connect to the database. As per your selection, further options will be shown on the screen to find the source either on the web network or a local computer. If required, you may have to authenticate your request by signing in to the particular service.
3. Now you have to choose the data which you want to import. Once you have connected to the database, you will see a new window “Navigator” on the screen.
4. It will show all the entities or tables from your data source. When you select a particular entity or table, it will display a preview of contents present in it.

1. Now you have two options to proceed further: You need to import the entities or tables by clicking on the Load button.
2. You can clean and transform the data before loading it in Power BI. To do so, you need to select the button “Transform Data.”

5. Once you have loaded the data in Power BI Desktop, you won’t be allowed to make any changes later. So, if only a specific customer set needs to be filtered, then use the Edit button to filter out the data first and then load it.
6. This will load all the data to Power BI Desktop, and you can now use it for further processing.

It is very simple to import data from Microsoft Excel to Power BI. You will learn how to get data from an excel workbook having a normal table present on your computer. Till now, you have seen how to import data via Power BI Desktop. Let us now see how you can do it via Power BI service.

Here are the steps to do so:

1. The first step is to check if the data present in the excel workbook table is in the correct format or not. You need to highlight the cell ranges, navigate to the “Insert” tab on the top of the screen, and choose “Table,” as shown in the below image.

2. You need to check that every column in the table has a valid and relevant name. This is required because it makes it easy to find relevant data while creating Power BI reports.
3. You can keep your file anywhere on your computer—no need to keep in a pre-defined particular drive like C drive or D drive. Just open Power BI, navigate to “Get Data” -> Files -> Local File. A dialog box will open where you have to select the Excel workbook you want to import.
4. Once the excel file is imported in Power BI, the report creation process can start. Note: It is not necessary for the excel file to be present on your computer only. It can be on SharePoint or OneDrive as well.

Once the above steps are completed, you will find a new dataset present in Power BI under the heading Datasets. You are now free to explore the data by creating dashboards and reports.

1. Choose the “Open Menu” icon present besides the dataset.
2. Now click on Explore. It will open a blank canvas on the screen.
3. Towards the right-hand side on the screen, you will find your tables as well their columns under the “Fields” section.
4. Now you have to choose the field(s) which you would like to present in your canvas visualization. Changing the visualization type, applying filters, and modifying relevant properties can be done from the “Visualizations” section.

Note: Data can also be imported using advanced features such as Power Query, Power View, and Power Pivot.

Connecting and importing from workbooks created in Microsoft Excel 2007 or later versions is supported by Power BI. All the workbook extensions must be in xlsx or xlsm format. The file size should be less than 1 GB.

Let us now see the different workbooks supported by Power BI.

Power BI supports worksheets having ranges of data, and they should be formatted as tables. You will be able to see the named tables as well as their columns in Fields pane while creating reports. It makes your work easy for visualizing data in the reports.

Power BI supports workbooks having a data model containing a single table or multiple tables in it. These tables are loaded in the data model with the help of Power Pilot, Power Query, or linked tables. Power BI supports all the data model properties like hierarchies, relationships, KPIs, and measures.

One thing to note here is that you are not allowed to share the data models with other Power BI users. Let us say one user who is logged in to Power BI using abc.com account is not allowed to share the workbook with another user who is logged in to Power BI using xyz.com.

Power BI supports workbooks using the Microsoft Excel for connecting to any external database in order to create dashboards and reports as the data is dependent on an external source. Not only this, but you are also allowed to create “Scheduled Refresh,” which automatically connects to the database for updates. This eliminates the need to refresh every single time using the Data ribbon manually. All the tiles present in the dashboards and the visualizations present in the reports will be automatically updated and synced with the data present in the data source.

The location of your workbook file plays an important role in order to display, or not to display, the charts, pivot tables, and power view sheets. Let us see how the file location impacts in the below section.

The workbook file can be located anywhere, like in your local computer, OneDrive, SharePoint, etc. Let us see one by one each of them and how each one is different.

1. Local Computer: The workbook file can be loaded into Power BI from your computer. Since the original file is already present in your local hard drive, therefore not the complete file will be imported to Power BI. The actual process that happens is the creation of a new dataset. All the data and other data model(s) are loaded from the workbook straight in the dataset. If there are any Power View sheets present in your workbook, they will be shown in the “Reports” section in the Power BI site. Microsoft Excel 2016 provides the “Publish” button directly in the File menu. It is the same as the “Get Data” feature of the Power BI with the only difference that you will find it easy to update the dataset using the “Publish” feature if you are a user who makes regular changes in the workbook.
2. OneDrive (Business): If you use your OneDrive for Business and Power BI with the same account, then it is the best possible method to keep all the work in sync like dashboards, reports, and datasets. As both OneDrive and Power BI use the cloud services, Power BI will automatically connect with the workbook file present in OneDrive in every 60 minutes. If there are any changes noticed in the workbook file, all the dashboards, reports, and datasets are updated automatically by Power BI. Similar to saving your workbook in a local drive, you are allowed to use the Publish feature for updating the reports and datasets immediately, else Power BI will do it for you in every 60 minutes.
3. OneDrive (Personal): If you have saved the workbook files in a personal OneDrive account, there will be many benefits similar to what you would have got with a Business account for OneDrive. The main difference is when you connect to the workbook file for the first time. You need to go to “Get Data” -> Files -> OneDrive (Personal) and then sign in using the Microsoft account (It will be different from the one you were using to connect to Power BI). One important point to note here is to use the “Keep me signed in” checkbox to make sure you are allowed to connect with the workbook in every 60 minutes. This will keep your reports and datasets in sync with the workbook file.
4. SharePoint Team: It is the same as saving your workbook file in OneDrive (Business). The only difference is that you need to connect to a workbook using Power BI only. Simply provide the URL for the same, or you can also connect with the root folder.

If you have used OneDrive to store your workbook files, then you can use the data present in Power Bi in 2 different ways. Let’s see both of them one by one.

1. Importing data in Power BI

If you are using the Import feature, all the supporting data present inside the tables or data model(s) will get imported to a newly created dataset. If there exist any Power View sheets as well, then they will get re-created in the form of reports in Power BI.

You are allowed to make changes into your workbook, and once they are saved, it will automatically get synced with the dataset every 60 minutes. If you want to get them synced instantly, you always have the option of using the “Publish” option to get it done immediately. All the visualizations present in dashboards and reports get updated as well.

In Microsoft Excel 2016, there is an option to directly Publish and Export.

2. Connecting, managing, and viewing Excel

If you are using the Connect feature, you will see your workbook similarly you see in Excel Online. You will also get additional features to pin down elements directly into the dashboard from the worksheets. You won’t be able to modify the workbook in this situation. If you want to make any changes, select the Edit button and make the changes in Excel Online or on your computer. Then all those changes will be saved in the workbook present on OneDrive.

You should go with this option only if you have got Charts, Pivot Tables, or Ranges to be pinned down in the dashboards.

In order to connect or import to an Excel workbook, you need to follow the below steps.

1. Open Power BI and navigate to NAV pane. Select the “Get Data” option, as shown in the below image.

2. Go to Files. Then select the Get button as shown in the below image.

3. Choose your file from the dialog box.
4. If the workbook is present on SharePoint or OneDrive, then click on the “Import” button.

If your Excel workbook is present in your local system, you need to follow the below steps.

1. Choose the “Local File” option from the menu. Navigate to the location where you have saved the workbook file.
2. Select the file and use the option “Upload file into Power BI.”
3. It will start uploading the workbook, and once it is done, a notification will be shown on the screen, saying that the workbook is now ready.
4. You will see your workbook in the Reports section.
5. One thing to note here is that when you use the “Import” option, it will only import the data which is present in the named table or inside the data model. It will show an error that “No data found in workbook” if the workbook does not have any named tables, data models, and Power View sheets.

After you have imported data into Power BI and created some reports, you should now focus on getting the saved file into Power BI service.

Let us see how you can publish it from Power BI Desktop using the below steps.

1. Go to Power BI Desktop and select File.
2. Now navigate to Publish -> Publish to Power BI, as shown in the below image.

3. Now provide your credentials for Power BI to sign in. This is required only for the first time and is a one-time activity.
4. Once the authentication is completed, a notification will be shown on the screen. It will have a URL link which will take you to the report in Power BI site.

Query parameters are added to the reports by the report owners. The main use of parameters is to allow the report creators to create specific parts of reports depending upon a set of parameter values. Let us say a report creator has created a parameter that will be restricting the report data to an individual region or country, which takes only certain predefined field formats such as text, time, and date.

Once the parameters are defined in Power BI Desktop, they will come along with the settings when you are publishing the report in the Power BI service. You are allowed to edit a few settings which describe or define the accepted values. But you won’t be able to edit the parameters restricting data to be used.

Here are the steps to do so:

1. Go to Power BI service and click the icon . It will open the Power BI “Settings.”
2. Go to the third tab “Datasets” from the top. Now highlight any dataset present in the drop-down list, as shown in the below image.

3. Expand the “Parameters” section present on the screen. If it doesn’t have any parameters associated with the dataset, it will display a message regarding the same. If there are parameters in the dataset, it will show all the parameters when you expand it.
4. Now you can review all the settings for every parameter. If you want to perform any changes, you can do it here itself. One thing to note here is that you won’t be able to edit the fields which are greyed out.

So this was how you could review and change the parameter settings for the datasets in Power BI Desktop.