Financial Analytics & Automation / Power BI Dashboard Creation Series
Introduction
In the previous article Vol. 2, we covered how to install Power BI Desktop and walked through its basic interface. You should now have a general understanding of the key components, such as the Ribbon, Report Canvas, and Data pane.
In this article, we’ll finally start working with data. We’ll use Power Query to import our sample data, set up headers, check and adjust data types, and add the columns needed for our KPI calculations. By the end of this process, we’ll have a clean data foundation ready for building our report.
As mentioned in Vol. 1, one of the key advantages of Power Query is that it records the data transformations you perform as a series of steps. The transformation steps we create here can be reused when new data becomes available in the following months. Once the process is set up, you don’t have to repeat the same data preparation from scratch every month.
Sample Data Used in This Series
In real-world environments, the data needed for analysis is often spread across multiple CSV files or database tables. Power BI allows you to bring these different data sources together and analyze them as a single data model.
The sample data used in this series is designed to simulate this kind of real-world setup. The structure is intentionally simple so that it is easy to follow, but it still gives you hands-on experience with the basic process of working with multiple tables in Power BI.
Download Sample Data for Creating a KPI Dashboard
| File Name | Contents | Main Columns |
|---|---|---|
| Dim_line_name.csv | Production Line Master | LineName |
| Fact_monthly_kpi.csv | Monthly Sales and Cost Data | Year/Month, Line Name, Net Sales, COGS, SG&A |
| Fact_defect_rate.csv | Data for Calculating the Defect Rate | Year/Month, Line Name, Production Quantity, Defect Quantity |
| Fact_equipment_utilization.csv | Equipment Utilization Rate Data | Year/Month, Line Name, Utilization Rate, Target Utilization Rate |
| Fact_inventory_turnover.csv | Inventory Turnover Data | Year/Month, Line Name, Product Category, Beginning Inventory, Purchases, Ending Inventory, COGS, Target Inventory Turnover |
Step 1: Import the First CSV File into Power Query
From the “Home” tab, select “Get data” → “Text/CSV.”
On the “Connect to data source” screen, click “Browse,” select the first sample file, Dim_line_name.csv , from the target folder, and click “Open.” On the screen that appears next, click “Next.”
When the “Preview file data” screen appears, click “Transform data.”
Step 2: Set Up Headers and Column Names
When the Power Query Editor opens, you’ll see the “Queries” pane on the left, the “Query Settings” pane on the right, and the data “Preview” in the center,
When data is imported, Power Query automatically detects column names and data types based on the contents of the CSV file. However, the results are not always correct.
In this example, the column in our imported line master is displayed as “Column1”. To fix this, go to the “Home” tab and click “Use First Row as Headers”. This sets the value in the first row, LineName, as the column name.
This transformation is recorded under “Applied Steps” in the “Query Settings” pane on the right.
When you later import new CSV files with the same structure and refresh the data, Power Query automatically re-applies these saved transformation steps. You don’t have to repeat the same operations manually each time.
Step 3: Import the Remaining Four CSV Files
Next, import the remaining four CSV files using the same procedure.
From the “Home” tab in the Power Query Editor, select “New Source” → “Text/CSV,” and import the following files one by one.
Fact_defect_rate.csvFact_equipment_utilization.csvFact_inventory_turnover.csvFact_monthly_kpi.csv
After importing each file, check that the column names have been set correctly.
Once all five files have been added, you should see five queries in the “Queries” pane.
Step 4: Check and Adjust Data Types
When data is imported, Power Query automatically assigns data types based on the contents of each column. However, the automatically detected type may not always be what you intended.
Incorrect data types can cause problems later when performing calculations, writing DAX, or creating relationships. Always check your data types after importing your data.
To change a data type, click the “data type” icon to the left of the column name.
Here are some of the main data type icons:
- ABC: Text
- 123: Whole Number
- 1.2: Decimal Number
- Calendar icon: Date
In this article, we’ll use the Fact_monthly_kpi table as an example. Be sure to check and adjust the data types in the other tables in the same way.
Step 5: Add Custom Columns for KPI Calculations
In a KPI dashboard, calculations are generally performed using DAX. However, since this series also covers the basics of Power Query, we’ll first use Custom Columns in Power Query to create some of the columns needed for our KPI calculations.
1. Create a Defect Rate Column (Fact_defect_rate)
Select the Fact_defect_rate query, and go to “Add Column” ➜ “Custom Column”.
In the dialog box, enter “Defect Rate” in the “New column name” field, and enter the following formula in the “Custom column formula” field.
[Defect Quantity]/[Production Quantity]
You can also insert a column name automatically by double-clicking the desired column under “Available columns” on the right.
After creating the column, change its “Data Type” to “Percentage”.
2. Create an Inventory Turnover Column (Fact_inventory_turnover)
Select theFact_inventory_turnover query and add another custom column using the same process.
Enter “Inventory Turnover” to the “New column name” field, and enter the following formula under “Custom column formula” filed.
[COGS]*12/(([Beginning Inventory]+[Ending Inventory])/2)
This formula annualizes monthly COGS by multiplying it by 12, then divides it by average inventory, calculated as the average of beginning and ending inventory, to estimate annual inventory turnover.
Because we are using monthly data in this example, we multiply COGS by 12. If you are working with annual data, no annualization is necessary.
After adding the column, change the “Data type” to “Decimal number.”
3. Create an Operating Profit Margin Column (Fact_monthly_kpi)
Finally, add a “custom column” to the Fact_monthly_kpi query.
Go to “Add Column” → “Custom Column”.
Enter “Operating Margin” as the “New column name”, and enter the following formula under “Custom column formula”:
([Net Sales]-[COGS]-[SG&A])/[Net Sales].
After creating the column, change the “Data type” of the Operating Margin column to “Percentage.”
Step 6: Close & Apply
Once you have finished editing all the queries, click “Close & Apply” on the “Home” tab.
Power Query Editor will close, and the transformations you configured will be applied to the data model in Power BI Desktop.
If you can see all five tables in the Data pane, your data preparation is complete.
Step 7: Perform a Final Check of Data Types and Formatting in Power BI Desktop
Even if you have already configured data types in Power Query, it is a good idea to check that the data types and display formats are correct in Power BI Desktop.
Power Query is used primarily to transform and prepare data, while Power BI Desktop manages data types and display formats within the data model. For this reason, it is important to perform a final check before building your report.
In Power BI Desktop, the “Column tools” tab allows you to check and change settings such as “column names”, “data types”, and “display formats”.
Take Fact_defect_rate as an example. Change the “Year/Month” column’s Format to “Short Date” so it’s easy to read. The “Defect Rate” column should already be set to “Percentage” (as configured in Step 5) — simply verify this setting here.
Repeat the same process for the other three tables.
Keeping data types and display formats properly configured will make the numbers and charts in your reports easier to read later.
Step 8: Save the Power BI file
Once you have completed the steps above, save your Power BI file.
Select “File” → “Save As,” and save it as “KPIDashboard.”
We save the file at the end in this tutorial, but in a real-world environment, it is better to save the file at the beginning of your work and save frequently using Ctrl + S.
From the next article onward, we’ll use this KPIDashboard file to continue building the report.
Frequently Asked Questions (FAQ)
1. Why do we multiply COGS by 12 when calculating Inventory Turnover?
Inventory turnover is normally calculated by dividing annual COGS by average inventory.
However, the data used in this example contains monthly COGS, representing only one month of activity. If we use the monthly figure as-is, the resulting inventory turnover will be lower than the annual turnover rate.
That’s why we multiply monthly COGS by 12 to annualize it before calculating the ratio.
If you are using annual data—that is, COGS already aggregated for the entire year—you do not need to multiply it by 12.
Always check whether your source data is monthly or annual and determine whether annualization is necessary.
2. Why aren’t Defect Rate and Operating Margin displayed as percentages?
Power Query creates custom columns as numeric values by default.
After creating a percentage-based column, verify both its Data Type and Format.
IWhen you create a custom column in Power Query, the calculation result is initially recognized as a numeric value. As a result, columns representing ratios, such as Defect Rate and Operating Margin, may not automatically be assigned the desired data type or display format.
After creating a ratio column, check its Data type and Format, and change it to Percentage where necessary.
Note that the settings work slightly differently in Power Query and Power BI Desktop.
In Power Query, you primarily configure the Data type, while in Power BI Desktop, you can configure both the Data type and Format.
See Steps 5 and 7 for more details.
3. What is the difference between a column calculated in Power Query and a measure created with DAX?
A column calculated in Power Query is evaluated when the data is refreshed, and the resulting values are stored as part of the table.
A DAX measure, on the other hand, is calculated dynamically based on the current filter context. Its result can change whenever filters or slicers are changed in a report.
4. Why should the “Year/Month” column remain a Date data type?
A column such as Year/Month may look like something that could simply be stored as text.
However, keeping it as a Date makes it easier to create a relationship with a Calendar table later in the process.
Date columns also work well with Power BI’s time-based analysis features, such as grouping by year, quarter, or month, displaying trends over time, and comparing different periods.
For this reason, it is generally recommended to keep date-related columns as the Date data type in Power BI.
If you want to change how the date looks, adjust the Format setting rather than changing the underlying data type.
5. Can I import multiple CSV files at once using Power Query?
Yes. If you need to import multiple CSV files with the same structure, you can use the Folder connector.
Power Query can automatically read and combine files in a folder, which can help automate the processing of CSV files that are added each month.
In this article, however, we import the five CSV files individually because each file has a different structure and serves a different purpose.
Summary
In this article, we used Power Query to import five CSV files, set up headers, check and adjust data types, and add custom columns needed for our KPI calculations.
One of the biggest advantages of Power Query is that the transformation steps you create are saved and can be reused. As long as the structure of your CSV files remains consistent, the same transformations can be automatically applied when you refresh the data in subsequent months.
This can significantly reduce the amount of repetitive work involved in monthly data preparation.
At this point, the data preparation required for analysis and visualization in Power BI is complete.
Coming Up Next
In Vol. 4, we’ll build a new Calendar table and connect all five tables through relationships to complete the Power BI data model. This will let us aggregate and analyze data accurately by month, production line, and product category. It’s an essential step that lays the foundation for DAX and Dashboard creation.