Creating an end-to-end Power BI project that focuses on listing the top 20 countries by external debt involves several key steps, from data acquisition to visualization. In this blog post, we will walk through each of these steps to demonstrate how to build a comprehensive Power BI project. By the end of this project, you will have an interactive report that showcases the top 20 countries with the highest external debt levels.

Dataset

Step 1: Data Collection and Preparation

The first step in any Power BI project is to collect and prepare your data. In this case, we need data on external debt levels for various countries. You can obtain this data from reliable sources like the World Bank, IMF, or other governmental agencies. Ensure that your data is in a structured format, such as a CSV or Excel file. I have taken from wikipedia

Step 2: Import Data into Power BI

  1. Open Power BI Desktop.
  2. Click on “Get Data” in the Home tab.
  3. Select your data source (e.g., CSV, Excel, or a database or web site link in my case wikipedia.
  4. Load the data into Power BI.

Step 3: Data Transformation

Once your data is loaded, you may need to perform some data transformation steps:

  • Ensure that date columns are recognized as dates.
  • Create calculated columns if necessary (e.g., converting debt values to a common currency).
  • Remove any unnecessary columns

Step 4:Create Visualizations

Now it’s time to create visualizations that will showcase the top 20 countries by external debt:

Table Visualization:

Drag the “Country/Region” and “External Debt” columns into a table visualization.
Sort the table by “External Debt” in descending order.


Bar Chart Visualization:

  • Create a bar chart to visualize the top 20 countries.
  • Drag the “Country/Region” to the “Axis” section.
  • Drag the “External Debt” to the “Values” section.
  • Limit the chart to display the top 20 items.


Additional Visualizations (Optional):

You can create additional visualizations like a map or a treemap to provide more insights into the data.

Step 5: Data Slicers and Filters (Optional)

Add slicers and filters to allow users to interact with the data. Users can filter the data by specific criteria, such as debt percentage of GDP or per capita debt.

list of countries data set for poer bi

Step 6: Calculated Measure

o split the data into two columns in Power BI using a delimiter, you can follow these steps: colum name external debt 

  1. Open your Power BI Desktop file.
  2. In the “Home” tab, click on “Edit Queries” to open the Power Query Editor.
  3. In the Power Query Editor, select the column that contains your data (in this case, “External debt US dollars”).
  4. Go to the “Transform” tab.
  5. In the “Transform” tab, click on “Split Column” and then select “By Delimiter.”
  6. In the “Split Column by Delimiter” dialog that appears, set the delimiter to a space (” “).
  7. Choose the option to split into “At the left-most delimiter” if you want to split the text into two columns from the leftmost space.
  8. Click “OK.” Your data will now be split into two columns in the Power Query Editor.
  9. Rename the columns to “units and “Debt Amount” by right-clicking on the column headers and selecting “Rename.”
  10. Close and apply the changes by clicking the “Close & Apply” button in the Power Query Editor.
  11. Your data should now be split into two columns in your Power BI data model, with “trillion billions ” and “Debt Amount” as the column names. You can use these columns in your visualizations and reports.
now in new column use following formula
Debt of Country or region = 
VAR ValueText = CONCATENATE([debt amount], [units])
VAR NumericPart = VALUE(ValueText)
RETURN
    SWITCH(
        TRUE(),
        [units] = "trillion", [debt amount] * 1E+12,
        [units] = "billion", [debt amount] * 1E+9,
        [units] = "million", [debt amount] * 1E+6,
        NumericPart
    )
this will give you value of debts and new column with units of measurement