T14/ Power BI - Tutorial: Connect data from a synchronized SharePoint List to Ms Project

In this Microsoft Power Automate tutorial, you will learn how to connect a SharePoint list synchronized to a Planning Ms project to Power BI.

👉 Discover the full training program

Create advanced reports to track your complex projects in real time.

To get started, on the Home tab, click Get Data . In the menu that appears, click More to see more data source options. In the search box, type SharePoint and the SharePoint List data source should appear in the list, click it .

Click on Sign in .

Now you need to copy and paste the link to your SharePoint site . Make sure you have this link handy. Select the 2.0 implementation, this is important to ensure the best compatibility and performance. Once you have done this, click OK .

Now you can select the lists you want to connect. To do this, simply check the boxes next to their names. Once you have selected all the lists you want, click Upload . And there you have it, you have now connected your SharePoint data.

Continuing our demo, we will now prepare the data for analysis. On the Home tab, click Transform Data . You will see that the Power Query editor opens. The first thing we will do is update the data types. To do this, select the column whose type you want to change. Next, on the Transform tab, we will change the data type of the columns. If you have columns with dates, make sure to change the type to Date . If you have numbers, choose Decimal . It is very important to verify that the data has the correct type, it is crucial for creating reliable and accurate reports. By following these steps, you will ensure that your data is correctly formatted and ready for analysis in Power BI.

To apply your changes, return to the Home tab and click Close and Apply .

For more basic editing of your data, you can also click Data View in the ribbon on the left. This feature allows you to make quick edits without having to enter the Power Query editor.

The column you see connected in Power BI are the ones that exist in the default SharePoint view. If you want to add new columns, you either need to view them in SharePoint or map new columns from MS Project.

First, open your MS Project schedule that is synced with SharePoint. Then, on the File menu, click Info . From there, you can start mapping the new fields. In our case, we'll map the Baseline Start and End Date columns.

Once you've mapped the new columns, click OK . Don't forget to save your changes to sync the new data.

Now, let's go back to SharePoint. In the default view, click on the three vertical dots and then click on Edit this view . Check the columns we just mapped.

And there you have it, you have added new columns to your data source.

Refresh your Power BI report, this will load the new columns into your report data. You should now see the new columns you added from SharePoint appear in your report data.

You now need to update the formats of the reference date columns to date, either from the Power Query editor or from the Data View.

To go further in our analysis and in order to create a report on the workload, we can add the Work and Summary columns. This will allow us to analyze the time spent by each resource and on each task. For the rest of this video, we will therefore map and connect these two columns to Power BI. This data is essential for a good analysis of the schedule and resources. By incorporating these elements into our data model, we will be able to have a detailed view of the distribution of work, which will help us optimize resource management and improve planning.

To integrate the Work and Summary columns into your Power BI report, you'll repeat the actions we performed in this tutorial: namely, mapping the fields from your Project schedule, displaying these columns in the default SharePoint view, refreshing the data in Power BI, and preparing this data in the Power Query editor or Data View.

To effectively leverage the Work column in our analysis, we need to transform it into a Decimal . To do this, we'll use Power Query in Power BI. First, open Power Query and click Refresh . Next, select the Work column. On the Home tab, click Replace Data . This will open a new window where you can make replacements to your data. In this window, replace "hour" with a blank field, and do the same for the space. Make sure to make both of these replacements to properly prepare the column for transformation. Now that the hours and spaces have been removed, you can change the type of the Work column to a Decimal .

Now connect the Summary column using the same steps.

Thanks for watching this tutorial. In the next part of this series, we'll show you how to leverage this data to create meaningful reports. We look forward to seeing you again for more of this tutorial.