Data Import & Power Query Editor Basics
Importing data from various sources (CSV, WEB, etc.), Using Power Query Editor to clean, transform, and prepare data
In this lesson, we will guide you through the process of importing data from different sources, such as CSV files, Web Data source, and teach you to clean, transform, and prepare your data for analysis.
Connecting a CSV file:
Here is the csv file which you can download and practice connecting with Power BI. Order-details.
1. Open Power BI Desktop from your computer. On the home screen, click on “Get data” from the Home ribbon.
2. A dialog box will appear with multiple data source options, select a data source Text/CSV, and click Connect.
3. Choose your file, and click Open.
Connecting to a Web Data Source
1. In the Get Data window, click the down arrow, select Web from the list of data sources. Click Connect.
2. A dialog box will appear asking for the URL. Enter the URL of the web data source (it can be a CSV file hosted online, an API link, or any web-accessible dataset). Click OK.
For example: You can search for “list of sovereign states by date of formation”, go to Wikipedia page and copy the URL.
https://en.wikipedia.org/wiki/List_of_sovereign_states_by_date_of_formation
3. When Power BI connects to a data source, it suggests tables in the Navigator window. These are structured collections of data that Power BI thinks might be useful for your analysis. Select tables and click on Transform Data.
• These were the steps to connect web data source to Power BI. Now we will jump to the Transformation
process and explore some important data cleaning tools provided by Power BI.
Why is Data Transformation Important?
• Before analyzing data or creating visualizations, it’s crucial to ensure your data is clean, organized, and in
the correct format. This is where Power BI’s Power Query Editor becomes a powerful tool. It helps you
removing unwanted columns, changing data types, filtering rows, and combining data from multiple sources,
all without needing to write code.
• Click the link below, an excel file will open in your browser click File(in the top left),choose Download option
then Microsoft excel to download the CountriesWorld dataset to practice Power Query Editor.
CountriesWorld.xlsx
• After downloading the CountriesWorld dataset, follow the steps below to get the data in Power BI.
Get data and Connect with Power BI
1. Open Power BI Desktop from your computer. On the home screen, click on “Get data” from the Home ribbon.
2. A dialog box will appear with multiple data source options, select a data source (Excel), and click Connect. Choose your file, and click Open
3. A Navigator window will appear. Check the box “Countries of the world” and click Transform.
1. Exploring the Query Editor
The Power Query Editor window has three key sections:
1. Queries Section: Located on the left, it shows all the queries (datasets) you are working on.
2. Steps Applied: Located on the right, this section tracks every transformation you make to your data, so
you can undo or redo steps. You can undo by clicking cancel symbol.
3. Data Grid: The main window where you see and transform your data.
4. Close & Apply: means you’re done making changes to your data, so you save them and go back to your
report to see the updated data.
2. Editing Rows (Remove Rows, Filter for Null Values)
• Remove Rows: You can remove unwanted rows, such as empty rows, rows with errors or duplicate rows. Go to the Home tab, click Remove Rows.
1. In this case we need to remove top 3 rows which are null, select first row and click Remove rows, choose option Remove top Rows.
2. Enter 3 in number of rows section then click OK.
• Filter Arrow (Null Values): You can filter out rows where a column has missing or null values.
1. In any column, click the small filter arrow at the top and uncheck (null) to filter out rows where the country name is missing.
3. Changing Data Types
Power BI allows you to change the data type for each column, such as converting text to numbers or dates.
Click the icon next to the column name and select the correct data type (e.g., Whole Number, Decimal, Date/Time). Change all data types of columns according to the data.
4. Replacing Values
You may need to replace certain values, like correcting punctuation in a column. You will get replace values in Transform panel.
• Example: In the population density column, you can replace a comma with a dot to standardize the values.
As our data has both comma and dot, but we have to keep only one, so we can replace dot with comma.
1. Right-click the column and choose Replace Values or you can go on Transform panel and choose
Replace Values from there as well.
2. Specify what you want to replace and the new value.
5. Extracting Characters
Power Query allows you to extract parts of a string from a column.
• Example: You can extract Rounded/Whole number from Literacy Rate column.
1. Select the Literacy Rate column, go to the Add Column tab, click Extract, and choose Text Before
Delimiter.
2. Enter the Delimiter as “.” Because we want to extract the whole part i.e. befaore point, click OK.
3. Scroll extreme right, a new column having Literacy rate in whole number will be added in your data set. You can change the column name by double click on existing column name.
In the next section, we will discuss about more advanced transformation tools that you can used to make customized columns in your dataset.
