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

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 on it .

Click Connect .

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've done that, click OK .

You can now select the lists you want to connect. To do this, simply check the boxes next to their name. Once you've selected all the lists you want, click Load . Voila, you have now connected your SharePoint data.

In the continuation of our demonstration, 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're going to do is update the data types. To do this, select the column whose type you want to change. Next, in the Transform tab, we'll change the data type of the columns. If you have columns with dates, be sure to change the type to Date . If you have numbers, choose Decimal number . It is very important to verify that the data has the correct type, it is a crucial aspect for the creation of reliable and accurate reports. Following these steps will ensure that your data is properly 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 those that exist in the default view of SharePoint. If you want to add new columns, you either have to show them in SharePoint or map new columns from MS Project.

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

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

Now back to SharePoint. In the default view, click the three vertical dots , then click Edit this view . Check the columns we just mapped.

Voila, you have added new columns to your data source.

Refresh your Power BI report, this action 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 must now update the formats of the reference date columns to date, from the Power Query editor or from the Data view.

To take our analysis further and to create a Workload report, 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 planning 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, map fields from your Project schedule, display these columns in the default SharePoint view, refresh data in Power BI, and prepare that data in Power Query Editor or Data View.

To effectively leverage the Work column in our analysis, we need to turn it into a Decimal Number . 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 in your data. In this window, replace "time" with an empty field, and do the same for the space. Be sure to do both of these replacements to properly prepare the column for transformation. Now that the hours and spaces have been removed, you can change the Work column type to Decimal .

Now connect the Summary column by following the same steps.

Thanks for following this tutorial. In the next part of this series, we'll show how to leverage this data to create meaningful reports. We hope to see you again for the rest of this tutorial.