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 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 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 have selected all the lists you want, click Load . And there you have it, you have now connected your SharePoint data.
In the rest 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 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 check that the data is of the correct type, this is a crucial aspect for creating reliable and accurate reports. Following these steps 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 which is synchronized with SharePoint. Then, on the File menu, click Information . From here you can start mapping the 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 sync the new data.
Now let's go back to SharePoint. In the default view, click the three vertical dots , then click Change 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 action will load the new columns into your report data. You should now see the new columns you added from SharePoint appear in your reporting data.
You must now update the formats of the reference date columns by 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 are going to 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 work distribution, which will help us optimize resource management and improve planning.
To integrate the Work and Summary columns into your Power BI report, you will repeat the actions that we performed in this tutorial: namely, map the fields from your Project schedule, display these columns in the default SharePoint display, refresh data in Power BI, and prepare that data in Power Query Editor or Data View.
To effectively use the Work column in our analysis, we need to transform it into a Decimal Number . To do this, we will 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 make these two replacements to properly prepare the column for transformation. Now that the times and spaces have been removed, you can change the type of the Work column to Decimal .
Now connect the Summary column using the same steps.
Thank you 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.
