How to Distribute a Single-Villa BOQ Across Five Villas

Introduction

In our current project, which consists of five villas, the provided Bill of Quantities (BOQ) only represents a single villa. This means that the total project cost is simply the cost of one villa multiplied by five. However, the BOQ is not structured into zones or floors, making it challenging to manage resources, track progress, and allocate costs effectively across all five villas. To streamline project execution, we must distribute the BOQ items into specific zones and floors during data loading. By using Excel Power Query, we can efficiently unpivot columns and categorize BOQ data, ensuring a structured and organized cost breakdown that enhances project management and financial tracking.

The Bill of Quantities (BOQ) is a critical document that outlines the quantities and descriptions of materials, labor, and tasks required. However, in many cases, the BOQ is not distributed into zones or floors, making it difficult to analyze, allocate, and manage effectively.

To improve project efficiency, it’s essential to divide BOQ items into specific zones and floors. This structured distribution helps track costs, manage resources, and streamline workflow. But how can this be done efficiently? The solution lies in Excel Power Query and data transformation techniques.

This article will guide you through the process of distributing BOQ items into zones and floors during data loading, using Excel’s Power Query to unpivot columns and automate categorization. By the end, you’ll have a clear understanding of how to structure BOQ data for improved project management.

We are going to explain every point in the theoretical and practical phases.

Understanding BOQ and Its Structure

1. Theory

Why is the BOQ Not Always Divided into Zones and Floors?

Many BOQs are prepared as a single list without specific zone or floor segregation. This can create difficulties in:

·         Tracking work progress by location

·         Budgeting for specific project sections

·         Material distribution and logistics

There is no practicality in this Point

Identifying Zones or buildings for BOQ Items

1. Theory

What Are Zones in Construction Projects?

Zones are defined areas within a construction site that help in dividing work efficiently. Examples include:

·         Buildings (e.g., Building 1, Building 2)

·         Phases of work (e.g., Phase 1, Phase 2)

·         Functional areas (e.g., Mechanical Zone, Electrical Zone)

There is no practicality in this Point

Preparing BOQ Data for Distribution

1. Theory

You can do it with one simple repetitive step, in which you can copy all the BOQ rows and paste them with different Building or you can add some columns and unpivot them in the Power Query,

In Power Query, unpivoting is the process of turning multiple column headers into rows, effectively normalizing or “flattening” your data. This is especially useful when your data has one or more columns that represent the values of a single categorical field.

Cleaning and Organizing BOQ Data

Before assigning zones and floors, ensure the BOQ is well-structured in the Cost loading sheet:

This video will show you how to create a cost-loading sheet

·         Standardize unit measurements

·         Ensure each item has a unique identifier

RECOMMENDED COURSE

Data Management and Reporting

Data Management is the key success of construction projects reporting. This self-study course will teach you using a step by step case…

• 1,680 students
• WhatsApp support group
• Certificate

View course

2. Practical

Formatting the BOQ for Easy Data transformation

A properly formatted BOQ will have:

1.       Separate columns for Item Description, Quantity, Unit, and Price

The Total cost of the single Villa is 6,868,632.77 and we need to multiply it by 5 and we need to differentiate every Villa

2.       An additional column for Zone and Floor

We added 5 columns for every Villa with the villa abbreviation.

3.       A structure ready for Power Query processing

At this stage, your BOQ is ready to be processed in Excel using Power Query.

Using Excel Power Query for Data Loading

1. Theory

Introduction to Power Query in Excel

Power Query is a powerful data transformation tool in Excel that allows:

·         Data cleaning and reshaping

·         Automating repetitive data tasks

·         Importing and merging data from multiple sources

2. Practical

Steps to Load BOQ Data into Power Query

1.       Open Excel and go to Power Query Editor

2.       Import the BOQ dataset (from an Excel file)

3.       Prepare for unpivoting columns to structure data

Now that we have loaded the BOQ into Power Query, the next step is to unpivot columns for easier data transformation.

4.       Choose the Columns that we need to unpivot as per the following pictures which are the 5 columns we add for the 5 Villas.

5.       After the unpivot, you can see two columns which are the Villa Name and the Villa Abb.

Then Close and Load these steps and check the New Total Cost of the Total project.

6.       You can see that the new Total Cost is 34,343,163.83 and this number is the cost of one villa multiplied by 5 the number of the villas.

If the Data is crashing and the data loaded is not accurate, please check this article “Why does data loading power query have errors”

In a multi-building construction project where the BOQ for a single structure needs to be applied across multiple villas, how can we use Excel Power Query’s “unpivot” feature and additional categorization columns to efficiently distribute BOQ items into distinct zones and floors?

To distribute a single-structure BOQ across multiple villas in Excel, begin by adding columns for each category you want to track—such as “Zone,” “Floor,” or “Villa.” Next, load the data into Excel Power Query, highlight the newly created categorical columns, and use the Unpivot feature. This transforms your wide data into a tall (normalized) structure by converting columns into rows under a single “Attribute” field (which can be renamed to “Zone” or “Floor”) and a corresponding “Value” field (e.g., “Quantity” or “Cost”). By doing this, you can systematically categorize and track BOQ items per zone, floor, or villa, thereby improving cost allocation, resource management, and overall project visibility.

Conclusion:

Distributing BOQ items into villas, Zones, and floors is key to effective project management, especially when replicating a single structure’s BOQ across multiple buildings. By leveraging Excel Power Query to unpivot columns and introduce relevant categorical fields, such as “Zone” or “Floor,” you can transform traditionally wide datasets into a more flexible and structured format. This approach not only simplifies cost tracking and budget control but also enhances resource allocation, monitoring, and overall visibility throughout the lifecycle of the project.

Download the Resources of this Article

How to Distribute a Single-Villa BOQ Across Five Villas 1.08 MB 410 downloads

This File is included in the following course(s)

Intermediate, Professional Planning

---

Keep in Touch

Join our Email Newsletters and receive FREE weekly useful content!

Your Name

Email

---

Related posts:

How to Create a Simple Procurement Report in Power BI What are the CSI 16 Divisions of Work Build your Dream First How to Edit the Activity name to include the Floor in 3 methods How to reduce total float in your schedule