You can download the file here or view the reports online
The last time I tried parsing an Xer file, I was using VBA and it was a horrible experience, anyway that was three years ago, The problem was not VBA per se, but supporting the different combination of Excel and Windows, I still receive sometimes emails from users complaining that Xer reader do not work for them, after a while I concluded it is not worth the effort.
Yesterday for no reason, I got this idea why not trying Powerquery , in 10 minutes I had a working prototype without writing a single line of code!!! even better it reads in bulk, copy a bunch of xer in the same folders, and there are loaded, only conditions they needs to have the same revisions( don’t mix P6 rev 7 with rev 8), as different revisions of P6 introduce some minor changes in the Xer schema, ( they add or remove some columns)
It is only a proof of concepts, I only loaded some tables, (activities and activities code), the data is loaded in the Data Model, and you need at least Excel 2013 with the free PowerQuery Installed.
Make sure you write the path to where your Xer is stored
You need to refresh the Queries here
Here is a pivot with some measures, now you can group only by Project and Activity codes and obviously by activity id.
I test it with two Xer that contains 47 projects
11 thoughts on “Using PowerQuery to read Primavera Xer file”
would like to know more about working in Power Query with data imported from Primavera
Could you please elaborate?
Myself a planning engineer by profession
LikeLiked by 1 person
I guess you need to understand primavera schema, you can read the documentation here
Thanks a lot for your file, I tried it with an xer of 1 project to see if working. I indicate the path in the excel and click on update all then I have the error : We couldn’t refreash the connection ‘query TASK’ here’s the error message we got : The column ‘create_user’ of the table wasn’t found.
Can you please help me
Thanks a lot
Primavera Xer files have sometimes different columns for different revision, in your case if you are familiar with PowerQuery, you can go to the editor and delete the column “create_user”
you can always use xer reader which does not require PowerQuery
I did go to editor but I could not find the column ” Create _User” can you please direct me where exactly I can find it .
can i have source file of above report
I can’t share the xer, but the source file for the report is in the blog post
would you please illustrate your steps in more details? I have no experience with PowerQuery and I really appreciated if you can walk me through it.
Thank you for this resource it has been a huge help in getting me setup with a usable reporting tool outside of P6. I’m curious if you’ve found a way to create resource curves using an XER file. I’ve been racking my brain trying to find a way and the closest path I can think of would be using R (or maybe another language) but haven’t yet found a solid script to get me there (I am very much a beginner with R).
Tying the resource curves assigned to activities is fairly straightforward but redistributing the curve across a dynamic duration is the biggest challenge I see.
I’d love to discuss further if you can.
However I get a run-time error on one project when I try to view the Gantt. The error reads “[Microsoft][ODBC Excel Driver] The connection for viewing your linked Excel Worksheet was lost.” Any idea what that is? When I debug, it takes me to this line “rssql.Open strsql, cnn”
Could anyone please help on to elaborate more. i was not able to follow the above steps