why does data loading power query have errors

I received an email from a planning engineer regarding a data loading problem created by Microsoft Excel Power Query, where he couldn’t identify the issue.

 This article discusses a problem you might face during project data loading and provides simple steps to fix it.

Power query data Loading Problem

Have you finished your Power Query steps, made changes to your data source (e.g., adding/removing columns), and found the query crashed?
After completing your project’s Data Loading and Cost Loading sheets, all the columns at the right were unpivoted at the query. You might realize that you need to add a new column to your Master BOQ like the screenshot below.

However, this could lead to a mismatch between the Activity Total Cost that shows the cost of your project from Power Query and the Total Cost in your Master BOQ, or it might even cause the data loading process to crash, Like the first screenshot. Identifying and fixing this issue can be time-consuming. Let’s walk through the solution to resolve it efficiently.

In the next screenshot, you can see that the correct total cost of the project is 91 Million.

Once you add the column to the data source (data loading sheet) and click ‘Refresh All’ in Queries and Connections, the pivot table connected to the query will update, reflecting the changed project total cost!

How to Solve the Problem?

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

First Solution

Step 1:

Don’t panic, from “data” open “Queries and connections” and open your data loading power query.

Step 2:

When the window opens, first you must check that there are no changes in your data types. If not, you must check your steps carefully to check that nothing happened to any of them.

In our case study, adding a new column caused step 4 to fail because unpivoted columns crashed.

New columns added to the master BOQ were linked to old steps, causing changes in total activity costs.

To fix this, ensure the new column is excluded from your steps. Remove it from the query if it doesn’t affect the cost, as shown below.

Another solution

you could rearrange your columns by moving the new column (in case you don’t want this new column to be included and unpivoted) to the beginning.

Once this step is complete, you can delete all unpivoted column steps and repeat the process.

click “close & load” and the magic will happen.

back to your activity cost sheet click “Refresh all” and wait for it to load.

The Total Activity Cost matches the correct BOQ total cost, as mentioned earlier.

For More helpful Tips, And Tricks to handle huge data and projects you can sign in to Construction Project Planning Intermediate Level with P6 the course also includes practical applications of planning techniques through a project management development plan. This plan will involve creating a project from scratch aimed at improving the project management abilities of planning engineers.

Download the Resources of this Article

why does data loading power query have errors 1.59 MB 1736 downloads

This File is included in the following course(s)

Intermediate

---

Keep in Touch

Join our Email Newsletters and receive FREE weekly useful content!

Your Name

Email

---

Related posts:

Understanding Merge Hotspot, Diverge Hotspot, and Logic Hotspot in Project Scheduling Build your Dream First How to reduce total float in your schedule How to Create the Narrative Report for Your Baseline Schedule How to Copy Progress Updates from one Project to another