Introduction

If you use Microsoft Power BI to develop analytical products for a customer, there is a high chance of either using excel or CSV(comma-separated value) file types. Microsoft Excel is one of Microsoft's most used applications. Its seamless integration with other applications like CRM(Customer Relationship Management) and ERPs(Enterprise Resource Planning) makes it almost an indispensable tool to be used in our journey into the Power BI world.

So in this article, I will take us through a short journey on how we could load an excel and csv file type into Microsoft Power BI, which is the first step in kickstarting the task of developing a report with Microsoft Power BI. Do have a wonderful tour going through this article.

How do we load data into Power BI using Excel as our data source?

Quick Note:

Carrying out this exercise, you should have Microsoft Power BI Desktop software installed on your laptop and have an excel and csv file available on your computer's local filesystem. these are the links to the files: superstore_Denormalized(Excel_File).xlsx and exams.csv. Clicking on these links takes you to a page like what we have below but that for the CSV will be slightly different.

For the excel file, you should see the download at the top-right of the second box-like shaped figure. Check out the attached image for a clearer understanding.

For the CSV file, you should see the picture below:

Click on the raw button, which should take you to a page that houses the raw data. On that page, right-click it and select save as. This should take you to a file system where you can select the location to save your file. After selection, you can proceed to save your file.

Now let's take a deep dive into loading data from an Excel file

1. Launch your Microsoft Power BI App, you should see an overlaying layer like what is displayed below. Click on the button at the top right to close it out.

   

2. After that has been done on the topmost layer, you should see a home tab, ensure you click on it, and when you look slightly below, you should see a Get Data Tab. Click on it

3. On clicking the get data tab, you should see a drop-down housing numerous connectors. To know more about connectors check out this link

   

4. Select the first connector named excel workbook connector, this should take you to your file system where you can locate your excel document.

5. On your local file system, search the location you downloaded the file, thereafter select your excel file, and head on to click Open.

   

6. The previous action leads you to a navigator page where you can select the dataset by ticking the box by your excel dataset.

   

7. After the table has been clicked, you should be able to preview your data and finally decide to load, Transform or Cancel. But in our situation we'll be loading the dataset, so you click on Load.

8. At this moment your data should start loading after loading go to the fields pane on your report View and check out the field pane where you should see your data loaded.

Congratulations! at this point you have completed loading your data into Power BI and are set to get working with your data.

Note that this completed process is for data that does not need cleaning. For datasets with some abnormality, you would have to transform through power query and not load directly through Power BI

How do we load data into Power BI using CSV as our data source?

Loading data using excel and CSV into Power BI is pretty similar to a great extent. The basic difference is the file format and how the data is formatted. In csv files, columns are separated by commas and each new row or record is started in a new line. The picture below shows how the CSV file is formatted.

The bulk of the work is done by Power BI by formatting the CSV files into a tabular format.

Let's dive right into loading a CSV file.

1. After your power BI Software has been launched, On the top-most tab, ensure that Home is selected and on the associated tabs below, Select get data, you should see a drop-down where you can spot TEXT/CSV, select that.

   

2. After you select Text/CSV, you should be directed to a local file system where you can select the location of your CSV file. After selection, click on Open.

   

3. At this point you should be in the Navigator where you can either load, transform, cancel or extract using examples. For this article, we'll be loading our data.

   

4. We should now see our data loaded into Power BI, where we can take action on our data.

Conclusion

In this article, I have been able to pictorially explain how we can start the very first execution activity of a data analyst, which is pulling the data for development into the Power BI software where the data can be acted on by different operations to build out an analytical product.