SharePoint (and Project Server) Shenanigans

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

Something I have been meaning to blog about for a while now is Power Query. There is nothing revolutionary in this post but hopefully it will give the readers some idea of how powerful Excel Power Query can be if they have not seen / used it before. We will only touch the surface here in this post but there is plenty of information available already.

So firstly, what is Power Query. In short, Power Query is an add in for Excel that enables you to work with many different data sources and transform that data as required. An intro can be seen here. Power Query is formula based but this is different formula syntax to what you use in Excel. The Power Query formulas are based on what was known a “M” language, an intro to the formulas can be found here.

Now we will look at creating a simple Excel Power Query report for Project Online using the OData API. The first report we will create will be a simple report that allows you to dynamically filter the data based on a text value in a cell. The dynamic filtering is useful when you are looking at large amounts of data such as time phased assignments in Project Online. The example we create below doesn’t really benefit from the dynamic filtering as the data set is small but this is only an example to show you how this works.

Get your Project ODATA url, for this example and am using the following:

https://<URL TENANT>/sites/pwa/_api/ProjectData/Projects()?$select=ProjectName,ProjectOwnerName,ProjectWork,ProjectCost,ProjectDuration,ProjectPercentCompleted,ProjectDescription,EnterpriseProjectTypeName

Launch Excel and click the Power Query tab then From Other Sources > From OData feed:

Paste in the URL:

Click OK. You will then be presented with an authentication window. Select “Organizational account” from the left hand side navigation and click Sign In. In the new window that appears enter the credentials for an account that has access to the ProjectData API.

Once authenticated you will see the Power Query Preview window load with the data:

You can see the formula used in the formula bar:

If you can’t see the formula bar, click the View tab and check the Formula bar check box:

We now have our Project Online data in Power Query ready to be used as a data source. In this example I have already selected the columns I wanted, but if you just used the Projects feed with no select (http://..pwa/_api/ProjectData/Projects()) you would see all of the columns displayed. At that point you can then easily chose which columns you would like in your data source by selecting the columns you don’t need then right click and chose Remove Columns.

Then you can see those columns have been removed. The formula bar has been updated with the latest steps:

You will also see another step appear in the applied steps, see it has the “Removed Columns” step:

At any point in time you can switch back to a previous step or even delete a step. If I click back on the source step I see the original data set with the two columns I deleted:

For the purpose of this post I will delete the “Removed Columns” step but before I do I want to show the Advanced Editor screen. This is available from the View tab:

This editor enables you to type the query yourself rather than using the UI menus. Some things you might want to do might not be possible from the UI and you are required to type / update the query. I will now delete the “Removed Columns” steps and you can see the query has been updated:

I can manually update the query update the data set, for example I can manually update the query to remove columns:

You will then see the data update and an applied step appear:

Now I am happy with my simple data set, but before I do anything I will update the query name to “Projects”:

Now chose “Close & Load To…” from the Home tab:

Then chose “Only create the connection” also check the check box to load the data to the data model:

You will then see the Excel sheet with the Workbook Queries window that allows you to preview the data:

Before we create the Excel report, I want be able to filter the data based on the Project Owner. Next insert a table like below:

Enter any Project Owner name for now, I used he MOD Administrator in this example as that user is the Project Owner for a few projects. Give the table a name, in this example I called it ProjectOwnerTable.

Now click the Power Query tab then “From Table”:

The Power Query editor will load with the following:

Switch to the advanced editor to see the query:

Update the query to change the type and select the record:

The record will then be displayed:

Now click the “Close & Load To…” from the Home tab and chose the “Only create connection” and check the check box to add the data to the data model.

Now load the first Projects query that was created in Power Query and switch to the advanced editor:

This needs to be modified to filter for the Project Owner in the new table that was created. See the updated query below:

You will need to enable the data in privacy option:

Click Close and load to save the changes in the Power Query editor and you will see the Excel worksheet:

Now you can create the Excel report, for this I will add a Pivot Table using an External Data source then selecting my data model:

Then update the pivot table with the fields:

Change the layout etc.

This is filtered to the projects where the owner is MOD Administrator, if I want to see other projects, update the Project Owner on row 2 and refresh the data:

A very quick and simple intro the Power Query for Excel with Project Online data using the ProjectData Odata API.

I hope to give more examples in the future.