Power BI Tutorial

Get Data from CSV File

In our previous articles, we learned about Power BI installation and User interface. We are now ready to create our first Data Source connection, retrieve the data using the Data Source connection. In this article, we will learn How to get data from a flat file or CSV file. For our first import, we will use the following sample CSV file.

FileDownload
Cartoon Characters sample textLogin to Download

In the above sample CSV file, we have data for famous Cartoon Characters. Download the above sample CSV file. After this, launch the Power BI desktop. If the start dialog (or screen) is opened, you can click on the Get Data option. You can also find the same option under the Home toolbar. 

After clicking on the Get Data it will open the "Get Data" dialog. Following is the image of the Get Data. In the "Get Data" dialog on the left, we have different categories to import the data. On the right, we have options of selecting any specific data source. At the time of writing this article, if all is selected, then Text/CSV is the second option in the data source lists.

If you not able to find the required data source type, you can filter the data source list. We are interested in Text/CSV, it comes under flat files. Select the file from the left. Now, our data source options are filtered. Right now, we have Excel, Text/CSV, XML, JSON, Folder, and SharePoint folder. Out of this, we need to select Text/CSV. 

If you are still not able to find you require the data source, you can use the search box. If any time you need to close the Get Data dialog, you can click on the cancel button or you can use the cross to close the Get Data dialog. This is all about Get Data Dialog, select the Text/CSV option and then click on the connect button.

After this, we need to select our CSV file in the file open dialog. Navigate to your file location and select your file. For example, our CSV file is available on the D drive. Select your file and click on the Open button.

In the next dialog, we need to provide the correct Delimiter for the files and the Data type detection option. For our sample CSV file Power BI able to detect the correct column delimiter. If Power BI is not able to detect the correct column delimiter, then manually select the correct delimiter from the drop down. In the delimiter drop down, we have following options

  • Tab
  • Colon
  • Comma
  • Equals Sign
  • Semicolon
  • Space
  • --custom--
  • --Fixed Width—

With the custom option, you can provide your column delimiter symbol. As shown in the following image we use a forward slash (/) as our column delimiter. It is wrong in the case of the current file. We use the forward slash (/) only to demonstrate how to use a custom column delimiter.

The last option is Fixed width. Again, with the Fixed width option, you manually provide the width for your column delimiter. Let me change the delimiter back to “Tab”.

The next option is used to detect the data types for each column. The first option is to detect the columns’ data type “Based on the first 200 rows”. It means Power BI will only scan the first 200 rows to identify the data type for each column. The second option is “Based on the entire dataset”. With the second option, Power BI will scan the entire file to identify the correct data type for all the columns. If we compare the performance and accuracy in between both the two options, then the performance is good with the first options. And accuracy is better with the second option. If we are using a very large data set, the second option will take some time to scan the entire data set. The alternative we have the last option “Do not detect the data type”. With this option, we can manually define the data type for each column. It is good if we need to consider both accuracy and performance. With the third option, our manual work will increase. In our sample CSV file, we have only 15 records so use the first option to detect the data type.

On the bottom, we have three buttons

Load: It will load our file data

Edit: It will allow us to perform more edition and transformation on our data. When we click on the Edit button, it will open the Power Query Editor window. We will discuss the same later in this article.

Cancel: It is used to cancel the current operation.

Let’s first check the Load option and later in this article, we will check the edit option.

After clicking on the load button Power BI will process and load the data. On the Fields pane now, you can see a new table entry with Name Cartoon Characters and its columns.

To add a simple list on our Report page, select the columns you want to add. For example, I want to add the First Name and the Last Name column. Click on the checkbox in front of First Name and the Last Name column. The alternative, you can drag-and-drop column either on the Report Canvas or value section. Congratulation, you created your first Power BI report visualization. It is a very simple list. We will create, the more complex visualization later in this tutorial series.

Let me copy the first visualization, press (CTRL+C) to copy and past it with (CTRL+V). It will create a duplicate visualization. Arrange both the visualization side by side. Now you have two different visualizations on our report page. Let’s add another column Gender, in our first visualization or list. If you have multiple visualizations on your report page, then ensure you are adding the new column in the correct visualization. We can add a new column either using value section or we can drag and drop it over the visualization element. If you need to add a new column using value section, then select the visualization first. Based on the selected visualization, you will see the previously used or added columns in the value section. Select the list in which you need to add the new column. Next, step to add the new column, either click on the checkbox in front of a new column or drag and drop it on the value section.

If you want to skip the selection for any specific visualization, then you can use drag and drop over visualization (not on value section).

We are done with the basic settings. At the time of getting data, we clicked on the Load button. Let’s check how Edit is different from Load. For this, we will start with a new Power BI report. This time from the Start Screen Dialog, select the resent sources options and then select our previously used CSV file. This time click on the Edit button. It will open a Power Query Editor.

It is a very strong tool to transform your data. We have a dedicated article for Power Query editor, we will learn more about Power Query Editor in our future articles. For this article, we will find the full name for the Cartoon Characters. To get the full name, we need to combine First Name and the Last Name column. To combine First Name and the Last Name, we will create a new custom column. To create a new custom column, click on the “Add Column” ribbon and then select the “Custom Column”. From keyboard type Alt + A then “CC”. It will open a dialog for custom column. For our new custom column, we will provide the name “Full Name” and under formula type Text.Combine({[FirstName],[Last Name]} , “ “). After adding formula for your new column if the syntax is correct, then click on the “OK” button to save and add a new Custom Column.

What we have done above, to find the FullName for Cartoon characters, we combined text for two columns “FirstName” and “Last Name” using Power Query Text.Combine function. We will learn more about Power Query functions in our upcoming articles. We have lots of options available in Power Query Editor. For the time being open Home ribbon and click on Close and Apply button. It will close the Power Query editor and apply all the pending Power Query Editor changes. Now, you can use the “FullName” column in your Power BI reports.

0 Comments
Leave A Comment

Please login to post your valuable comments.

share