Originally posted on LinkedIn.
Overview
This guide provides step-by-step instructions for extracting and transforming project task data from Microsoft Project Online using Power Query. By leveraging the Project Online Reporting API, you can pull structured JSON data into Excel or Power BI, convert it into a usable table, and apply necessary transformations for better reporting. This approach allows users to efficiently analyze project metrics, track task progress, and automate data retrieval.
What is the Project Online Reporting API?
Microsoft Project Online provides a Reporting API that allows users to extract project and task data in a structured format. This API is a REST-based service that delivers project data in JSON format, making it easy to integrate with reporting tools like Power BI and Excel. By accessing the API, users can retrieve key project metrics, track task progress, and generate custom reports.
Test Your Access Using Your Browser
You can explore the Project Online Reporting API directly in your web browser by navigating to the root node. To do this:
- Open your browser and log in to Project Online.
- Edit the following URL, replacing yourcompany with your actual SharePoint site name and pwa with your actual site URL if needed: https://yourcompany.sharepoint.com/sites/pwa/_api/ProjectData
- Press Enter to load the page.
- The browser will display a structured JSON response showing available datasets such as Projects, Tasks, Assignments, and more.
This is a useful way to verify API connectivity and explore available fields before writing queries.
Step 0: Open Power Query in Excel or Power BI
Before extracting data, you need to open Power Query. Follow these steps based on your tool:
In Excel:
- Open Excel and navigate to the Data tab.
- Click on Get Data > From Other Sources > Blank Query.
- This will open the Power Query Editor where you can enter your query.
In Power BI:
- Open Power BI and click Common data sources from the Home ribbon.
- Select Blank query. This will launch the Power Query Editor where you can enter your query.
Step 1: Define the Fields to Extract
There are 97 standard fields available in the Project Online Reporting API. To improve performance and simplify data processing, it is important to limit the selection to only the fields that are necessary for your report. We’ll put those fields in a JSON list for use later:
Step 2: Retrieve Data from the Reporting API
Use the Web.Contents function to call the Reporting API with the selected fields.
Note: The parameters PWASiteUrl and ProjectUID must either be created or replaced with hard-coded values.
- Example PWASiteUrl: https://yourcompany.sharepoint.com/sites/yoursitename
- Example ProjectUID: 95d45d20-bb75-ef11-acfc-a2f1439cd6a1
You can find your ProjectUID by navigating to the project in Project Online and checking the URL.
If you need help creating and editing parameters in Power Query, refer to Microsoft’s documentation: Parameters – Power Query | Microsoft Learn
Step 3: Convert JSON Response to Table
Convert the JSON response into a structured Power Query table.
Step 4: Set Data Types for Columns
Apply proper data types to ensure accuracy in calculations and reporting:
Step 5: Convert API Date Fields to Readable Format
Microsoft Project Online returns date values in a Unix timestamp format enclosed in a string (e.g., /Date(1726592400000)/). These timestamps represent the number of milliseconds since January 1, 1970 (the Unix epoch). Excel and Power BI, however, use a different date system, where dates are stored as serial numbers starting from January 1, 1900.
To correctly interpret these timestamps:
- First, extract the numeric value from the string using Text.BetweenDelimiters(_, “(“, “)”).
- Convert the extracted milliseconds to days by dividing by 86,400,000 (the number of milliseconds in a day).
- Adjust the resulting value by adding 25,569, which represents the number of days between January 1, 1900, and January 1, 1970, to align with Excel’s date system.
The following transformations ensure that all date fields are properly converted into a human-readable datetime format:
Try It Yourself!
To get started, open a blank query in Power Query by following these steps:
- Open Excel and navigate to the Data tab.
- Click on Get Data > From Other Sources > Blank Query.
- In the Power Query Editor, open the Advanced Editor (found under the Home tab).
- Paste the following code into the Advanced Editor window and click Done.
Now that you understand the process, copy and paste the full Power Query script into your Power Query Editor and run it against your Project Online data. Experiment with modifying the selected fields or adding additional transformations to customize the output for your needs.
Full Example Code
Here in this LinkedIn post you will find the complete Power Query script for extracting and transforming Project Online task data. Use this as a starting point for your own reports.
Conclusion
This Power Query script efficiently extracts task data from Microsoft Project Online, transforms JSON responses into a structured table, and applies necessary formatting. You can use this approach to automate data retrieval and improve reporting in Excel or Power BI.
Learn More + Earn 1 PDU!
Join us to learn live and earn 1 PDU! In this live, hands-on webinar, we’ll walk you through the step-by-step process of using Power Query to seamlessly connect, transform, and analyze data from Microsoft Project Online. Whether you’re a project scheduler, data analyst, or Power BI enthusiast, this session will give you the tools to automate your workflows and build dynamic reports!
Don’t miss this opportunity to see in-depth demonstrations and ask questions in real time! Exclusive for MPUG members.
Elevate your project management skills and propel your career forward with an MPUG Membership. Gain access to 500+ hours of PMI-accredited training, live events, and a vibrant online community. Watch a free lesson and see how MPUG can teach you to Master Projects for Unlimited Growth. JOIN NOW
