Table of Contents

Configuring project for the web project and task sync

Note

No changes are needed to the Project for the Web integration when it is deprecated and replaced by Planner. The integration will remain functional and continue to work as expected.

The following videos walk-through the setup process covered in more detail below:

After setting up the dataflow, don't forget to setup the External System entry:

Note

There is no audio in these videos

Data Flow Query Name Sync Table
Project sensei_msdyn_project sensei_msdyn_project
Task sensei_p4tw_task_delta sensei_p4tw_task_delta
Task sensei_p4tw_assignment sensei_p4tw_assignment

To connect a Dataflow to your Project for the web Dataverse, you can follow these steps:

  • Navigate to https://make.powerapps.com and log in with your administrative account
  • Ensure that you have selected the Power Apps Environment that contains your Altus installation
  • From the Quick Launch menu, select to expand Data and then select Dataflows

If the sample Dataflows already exist,

  • Locate the Dataflow that you wish to configure, open the ellipsis (…) menu and then select Edit (then continue with the steps below from 'Populate the SourceUrl...':

If the sample Dataflows do not exist,

  • Select New dataflow > Import template (Preview)

This image displays the Power Apps import template menu

  • Select the .pqt file that you wish to upload the Dataflow template for. (Links to the example Dataflows are in the Initial Setup section)
  • Click Create

This image displays the Dataflow import page and it's fields

  • Populate the P4TWUrl parameter with the Url of your Power Apps environment with your P4TW data, for example: https://orgabcd1234.crm.dynamics.com/
  • Populate the SenseiIQUrl parameter with the Url of your Power Apps environment that is hosting Altus. For example: https://org1303d343.crm.dynamics.com
Note

In cases where you are combining data from multiple M365 tenants (i.e. Altus in one Microsoft tenant and PWA in another, please ensure that you select Options from the ribbon bar (Project Options) and check 'Allow combining data from multiple sources'

This image displays the project Options pane and it's fields

  • Select the dataset query from under the Queries heading, then select to Configure connection
  • If there is no existing connection for your Dataflow, select 'Create new connection'
  • Enter the following details:
Column Value
Connection name {Enter a name for your connection (if not populated automatically)}
Authentication kind Organizational account
Privacy level Organizational
  • Select Sign in and enter the credentials of an account which has access to all project data in the environment that wish to connect to.
Note

If you are using the same account to log in to the source environment as the one accessing this environment, still choose to enter the credentials afresh (rather than selecting the already logged in account) - for whatever reason, selecting the account (rather than re-entering the credentials) does not seem to have a high success rate in terms of the data flowing through upon creation of the connection.

  • Select Connect
Note

The data imported will be equivalent to the data accessible by the user identity used to connect. It may be appropriate to use an environment administrative account in the PFTW environment to connect to get a complete representation of the data imported.

  • If successful, data from the external system will load to the preview in the Power Query window.
  • Select the Next button in the bottom right of screen
  • Ensure that the Parameters Queries are configured with the Load setting set to Do not load (this information does not need to logged anywhere).
  • Follow these steps for each remaining query in your dataflow:
    • Select a Query a query from the left hand panel.
    • Select Load to existing table. And select the correct destination table (see reference in this article)
    • Check the 'Delete rows that no longer exist in the query output' checkbox.
    • In the right hand panel map all of the columns present. Click 'Auto Map' to complete this quickly.
    • Select to run your Dataflow on a schedule. The frequency depends on both your business requirements as well as the volume of data that needs to be sent, particularly if you are synchronizing tasks. We recommend running dataflows once per day.
    • Verify that your Dataflow runs successfully.
Note

Project for the Web renders dates in the UI as localized to the project settings, but stores them in UTC time. In order to ensure that the dates imported into Altus are the same as what you are viewing in project, please set the timezone of the account that owns the dataflow to be the same as the remote project for the Web. Otherwise, dates will be timezone adjusted to whatever the account settings are.

The dataflow column mappings for each loadable query are shown below.

  • sensei_msdyn_project Dataflow Mapping: This image displays the Power Query Column mapping configuration page for projects
  • sensei_p4tw_task_delta Dataflow Mapping: This image displays the Power Query Column mapping configuration page for tasks
  • sensei_p4tw_assignment Dataflow Mapping: This image displays the Power Query Column mapping configuration page for assignments
Note

If the Dataflow contains a query called Tasks_Raw, select 'Do not load' as the Load setting for that Query. (It is used internally to the Dataflow only).

Once the dataflow is running and populating the sync tables, you're ready to complete the second part of the configuration process, which consists of the following steps:

  • Enable the Disabled External System Record(s) by navigating to the 'Settings' area, then to 'External Systems'. Change the view to 'Inactive External Systems' and select the record for the system you wish to activate and click 'Activate' from the ribbon.

This image shows the Inactive External Systems page and how to Activate an item

  • Once the record is active, review the column mappings by opening the External System record and navigate to the ‘Projects’ tab. Altus has mapped the standard columns that we typically see our clients using in project for the web and project Online to columns that exist for an Altus project. If there are new or additional columns that you require to be mapped, first, ensure that your data flow is configured to load the new column data into a new column on the target table, then create a ‘Metadata Mapping’ for the column desired to a column in the sensei_project table. This will ensure that the data retrieved by the data flow will carry through to the Altus project that is linked to the External project.

  • Configure the ‘Project URL Pattern’ to provide a direct link to the project in the external system. In some instances, the “Pattern” for Project for the Web projects needs to be updated with the org name as follows: https://project.microsoft.com/en-us/?org=org5cc93e48.crm.dynamics.com#/taskgrid?projectId={ID}, where the text above would be replaced with your org ID in the format: ?org=yourP4TWOrg.crmX.dynamics.com

Note

If your installation of Project for the Web is in a non-default environment, then you will need to update the Project URL Pattern (and Task URL Pattern) to point to the Microsoft provided 'Project' model driven app in the environment where Project for the web is located (rather than the project.microsoft.com url). The Project URL pattern will look something like this: https://org5cc93e48.crm.dynamics.com/main.aspx?appname=msdyn_ProjectServiceCore&pagetype=entityrecord&etn=msdyn_project&id={ID} - but you will need to substitute your unique org ID into the above pattern (e.g. yourP4TWOrg.crmX.dynamics.com at the start of the url pattern).
The Task URL Pattern will look something like this: https://org5cc93e48.crm.dynamics.com/main.aspx?pagetype=entityrecord&etn=msdyn_project&id={sensei_p4tw_projectid_key}&extraqs=msdyn_TaskId={ID}&TabToFocus=PexWebTab - but again, you'll need to substitute your unique org ID into the above pattern.

This image displays the Project URL Pattern on the Projects tab

  • Finally, on the ‘Details’ tab of the ‘External Project’ record, there is a ‘Icon URL’ column. This displays the base64 encoded icon image that represents this type of external project in the external project selector on the sensei_project form and also in the Power BI Insights reports. It shows what system the external project record originated from. The values for these ‘Icon URL’s are configured correctly for Project for the Web and Project Online projects, however, if you are setting up a different type of system or want to change the icon for whatever reason, please enter a new base64 encoded icon image URL into this column. Jump to: Icon URL Reference This image displays the Icon URL on the Details tab

This image displays the link to the external project link

Use the tables below as a reference for the default configuration of each of the metadata mapping tables.

Project

Project Sync Table Name: sensei_msdyn_project

Project URL Pattern: https://project.microsoft.com/en-us/#/taskgrid?projectId={ID}

Note

See the Note above if your Project for the web installation is in a non-default environment

Name External Column Name External Column Type
ID sensei_msdyn_projectid_key ID
sensei_effortcompleted sensei_msdyn_effortcompleted
sensei_effortremaining sensei_msdyn_effortremaining
sensei_efforttotal sensei_msdyn_effort
sensei_name sensei_msdyn_subject Name
sensei_percentcomplete sensei_msdyn_progress
sensei_projectfinish sensei_msdyn_finish
sensei_projectstart sensei_msdyn_scheduledstart
sensei_proposedstart sensei_msdyn_taskearlieststart

Task

Task Sync Table Name: sensei_p4tw_task

Task Sync Delta Table Name: sensei_p4tw_task_delta

Task URL Pattern: https://project.microsoft.com/en-us/#/taskboard?projectId={sensei_p4tw_projectid_key}&taskId={sensei_p4tw_taskid_key}

Note

See the Note above if your project for the web installation is in a non-default environment

Name External Column Name External Column Type
sensei_bucket sensei_bucket Bucket
sensei_duration sensei_duration
sensei_effort sensei_effort
sensei_effortcompleted sensei_effortcompleted
sensei_effortremainingoverride sensei_effortremaining
sensei_externalparenttaskid sensei_p4tw_parenttaskid_key Parent Task ID
sensei_externalprojectid sensei_p4tw_projectid_key Parent Project ID
sensei_externaltaskid sensei_p4tw_taskid_key ID
sensei_index sensei_index
sensei_milestone sensei_milestone Is Milestone
sensei_name sensei_name Name
sensei_order sensei_order Bucket Order
sensei_percentcomplete sensei_percentcomplete
sensei_summary sensei_summary
sensei_taskfinish sensei_taskfinishdate
sensei_taskstart sensei_taskstartdate

Assignment

Assignment Sync Table Name: sensei_p4tw_assignment

Name External Column Name External Column Type
sensei_externalassignmentid sensei_p4tw_assignmentid_key ID
sensei_externalresourceid sensei_p4tw_resourceid_key Resource ID
sensei_externaltaskid sensei_p4tw_taskid_key Parent Task ID
sensei_name sensei_name Name

Troubleshooting Date Issues

Earlier versions of the Dataflows for Project for the web data sometimes resulted in inconsistent translation of Date fields for Projects and Tasks.

Project for the web currently aligns Project and Task times according to the calendar associated to the Work Hour Template for the Project.

Project (msdyn_project) includes a msdyn_workhourtemplate Lookup field to msdyn_workhourtemplate entity. msdyn_workhourtemplate includes a field called msdyn_calendarid - which is a plain text field, but represents the guid of the associated calendar record. Querying for the calendar record with that calendarid and including calendar rules in the query will result in a timezonecode field in the calendar rules. That timezonecode can then be used to query the timezonedefinitions entity and retrieve the bias field value (which will represent the number of minutes that the date values need to be offset by).

This logic can all be applied within the Project and Task dataflows (and is included in the latest version of those Dataflow templates).

Project Dataflow

For environments where a previous version of the Dataflow has already been deployed (and possibly customised), in the Project Dataflow, the core logic change can be applied by editing the sensei_msdyn_project query in Advanced Mode and carefully merging in the changes highlighted in green below. Items to remove are highlighted in red;

let
  GetTimeZoneCode = (calendarid) =>
    let
      calendarEndpoint = Text.Combine({Text.Trim(P4TWUrl, "/"), "/api/data/v9.1/calendars(", calendarid, ")?$expand=calendar_calendar_rules&$filter=versionnumber%20ne%201686191119339"}),
      RawData = Web.Contents(calendarEndpoint),
      Json = Json.Document(RawData),
      timezonecode = Json[#"calendar_calendar_rules"]{0}[#"timezonecode"]
  in timezonecode
  ,
  GetTimeZoneOffset = (timezonecode) =>
    let
      timezonedefinitionsEndpoint = Text.Combine({Text.Trim(P4TWUrl, "/"), "/api/data/v9.2/timezonedefinitions?$filter=timezonecode%20eq%20", Number.ToText(timezonecode)}),
      RawData = Web.Contents(timezonedefinitionsEndpoint),
      Json = Json.Document(RawData),
      timezoneOffset = Json[#"value"]{0}[#"bias"]
  in timezoneOffset
  ,
  ConvertDateTimeToProjectTimeZone = (dateValue, timezoneOffset) =>
  let
      convertedDateTimeValue = dateValue + #duration(0,0,-timezoneOffset,0)
  in convertedDateTimeValue
  ,
   #"Format P4TWUrl OData Source" = Text.Combine({Text.Trim(P4TWUrl, "/"), "/api/data/v9.1/"}),
  #"Get P4TW Data" = OData.Feed(#"Format P4TWUrl OData Source", null, [Implementation = "2.0", ODataVersion = 4, IncludeAnnotations = "OData.Community.Display.V1.FormattedValue"]),
  #"Navigate to msdyn_project table" = #"Get P4TW Data"{[Name = "msdyn_projects", Signature = "table"]}[Data],
  #"Remove Columns" = Table.RemoveColumns(#"Navigate to msdyn_project table", {"_owningteam_value", "_ownerid_value", "_modifiedonbehalfby_value", "msdyn_bulkgenerationstatus", "versionnumber", "traversedpath", "_msdyn_msprojectdocument_value", "msdyn_globalrevisiontoken", "statecode", "processid", "_modifiedby_value", "_msdyn_projectmanager_value", "importsequencenumber", "_msdyn_workhourtemplate_value", "statuscode", "msdyn_disablecreateofteammemberformanager", "_msdyn_contractorganizationalunitid_value", "timezoneruleversionnumber", "_stageid_value", "overriddencreatedon", "_owninguser_value", "_owningbusinessunit_value", "_createdonbehalfby_value", "_msdyn_replaylogheader_value", "utcconversiontimezonecode", "_createdby_value", "msdyn_calendarid"}),
  //expand to get calendarid from workhourtemplate
  Expand = Table.ExpandRecordColumn(#"Remove Columns", "msdyn_workhourtemplate", {"msdyn_calendarid"}, {"calendarid"}),
  //get distinct calendarids
  DistinctCalendarIds = Table.SelectColumns(Table.Distinct(Expand, "calendarid"), "calendarid"),
  //for each distinct calendarid, run odata query to add column that contains timezone code information
  AddTimeZoneCode = Table.AddColumn(DistinctCalendarIds, "timezonecode", each GetTimeZoneCode([calendarid])),
  //for each timezone code, run odata query to get timezone information and add column which includes the bias (offset)
  AddTimeZoneOffset = Table.AddColumn(AddTimeZoneCode, "timezoneoffset", each GetTimeZoneOffset([timezonecode])),
  //now need to join back to the original table so we can then try to apply the offsets to the datetime fields
  JoinedProjectsWithTimezones = Table.NestedJoin(Expand, {"calendarid"}, AddTimeZoneOffset, {"calendarid"}, "timezonedetails", JoinKind.LeftOuter),
  ExpandTimezones = Table.ExpandTableColumn(JoinedProjectsWithTimezones, "timezonedetails", {"timezoneoffset"}, {"timezoneoffset"}),
  ConvertDateTimes = Table.FromRecords(
      Table.TransformRows(
      ExpandTimezones,
      (r) =>
      Record.TransformFields(
        r,
        {
          {"msdyn_finish", each ConvertDateTimeToProjectTimeZone(_, r[timezoneoffset])},
          {"msdyn_scheduledstart", each ConvertDateTimeToProjectTimeZone(_, r[timezoneoffset])},
          {"msdyn_taskearlieststart", each ConvertDateTimeToProjectTimeZone(_, r[timezoneoffset])}
        }
      )
    ),
    Value.Type(ExpandTimezones)
  ),
   #"Reformat Date Columns" = Table.TransformColumns(ConvertDateTimes, {{"msdyn_finish", each DateTimeZone.ToText(_, "yyyy/MM/ddThh:mm:sszzz"), type text}, {"msdyn_scheduledstart", each DateTimeZone.ToText(_, "yyyy/MM/ddThh:mm:sszzz"), type text}, {"createdon", each DateTimeZone.ToText(_, "yyyy/MM/ddThh:mm:sszzz"), type text}, {"msdyn_taskearlieststart", each DateTimeZone.ToText(_, "yyyy/MM/ddThh:mm:sszzz"), type text}, {"modifiedon", each DateTimeZone.ToText(_, "yyyy/MM/ddThh:mm:sszzz"), type text}}),
  #"Rename Columns for Auto map" = ...

Task Dataflow

For environments where a previous version of the Dataflow has already been deployed (and possibly customised), in the Task Dataflow, the core logic change can be applied by editing the Tasks_Raw query in Advanced Mode and carefully merging in the changes highlighted in green below. Items to remove are highlighted in red;

let
  GetTimeZoneCode = (calendarid) =>
    let
      calendarEndpoint = Text.Combine({Text.Trim(P4TWUrl, "/"), "/api/data/v9.1/calendars(", calendarid, ")?$expand=calendar_calendar_rules&$filter=versionnumber%20ne%201686191119339"}),
      RawData = Web.Contents(calendarEndpoint),
      Json = Json.Document(RawData),
      timezonecode = Json[#"calendar_calendar_rules"]{0}[#"timezonecode"]
    in timezonecode
  ,
  GetTimeZoneOffset = (timezonecode) =>
    let
      timezonedefinitionsEndpoint = Text.Combine({Text.Trim(P4TWUrl, "/"), "/api/data/v9.2/timezonedefinitions?$filter=timezonecode%20eq%20", Number.ToText(timezonecode)}),
      RawData = Web.Contents(timezonedefinitionsEndpoint),
      Json = Json.Document(RawData),
      timezoneOffset = Json[#"value"]{0}[#"bias"]
    in timezoneOffset
  ,
  ConvertDateTimeToProjectTimeZone = (dateValue, timezoneOffset) =>
    let
      convertedDateTimeValue = dateValue + #duration(0,0,-timezoneOffset,0)
    in convertedDateTimeValue
  ,
   BaseUrl = Text.Combine({Text.Trim(P4TWUrl, "/"), "/api/data/v9.1/msdyn_projecttasks?$select=statecode,msdyn_scheduledend,msdyn_subject,_msdyn_project_value,msdyn_projecttaskid,msdyn_ismilestone,msdyn_summary,msdyn_start,msdyn_duration,msdyn_progress,msdyn_effort,msdyn_effortcompleted,msdyn_effortremaining,msdyn_displaysequence,_msdyn_parenttask_value&$expand=msdyn_projectbucket($select=msdyn_name,msdyn_displayorder)&$filter=statecode eq 0"}),
  GetUserId = () =>
    let
      WhoAmIUrl = Text.Combine({Text.Trim(P4TWUrl, "/"), "/api/data/v9.0/WhoAmI"}),
      RawData = Web.Contents(WhoAmIUrl),
      Json = Json.Document(RawData),
      UserId = Json[#"UserId"]
    in UserId,
  GetDateFormat = () =>
    let
      UserId = GetUserId(),
      SettingsUrl = Text.Combine({Text.Trim(P4TWUrl, "/"), "/api/data/v9.0/usersettingscollection?$filter=systemuserid%20eq%20", "'", UserId, "'"}),
      RawData = Web.Contents(SettingsUrl),
      Json = Json.Document(RawData),
      FormatString = Json[#"value"]{0}[#"dateformatstring"]
    in FormatString,
  ConvertDate = (dateString, formatString) =>
    let
      newFormatString = if Text.Contains(dateString, "-") then Text.Replace(formatString, "/", "-") else formatString,
      newFormatString2 = if Text.Contains(dateString, ".") then Text.Replace(newFormatString, "/", ".") else newFormatString,
      ConvertedDate = DateTime.FromText(dateString, [Format = newFormatString2])
    in ConvertedDate,
  DateFormatString = GetDateFormat(),
  //get all projects so we can determine timezone offset based on PFTW Project Calendar
  ProjectBaseUrl = Text.Combine({Text.Trim(P4TWUrl, "/"), "/api/data/v9.1/msdyn_projects?$select=msdyn_projectid&$expand=msdyn_workhourtemplate($select=msdyn_calendarid)"}),
  ProjectTable = OData.Feed(ProjectBaseUrl, null, [ ODataVersion = 4, Implementation = "2.0", IncludeAnnotations="\*"]),
  ExpandWorkHourTemplate = Table.ExpandRecordColumn(ProjectTable, "msdyn_workhourtemplate", {"msdyn_calendarid"}, {"calendarid"}),
  //get distinct calendarids
  DistinctCalendarIds = Table.SelectColumns(Table.Distinct(ExpandWorkHourTemplate, "calendarid"), "calendarid"),
  //for each distinct calendarid, run odata query to add column that contains timezone code information
  AddTimeZoneCode = Table.AddColumn(DistinctCalendarIds, "timezonecode", each GetTimeZoneCode([calendarid])),
  //for each timezone code, run odata query to get timezone information and add column which includes the bias (offset)
  AddTimeZoneOffset = Table.AddColumn(AddTimeZoneCode, "timezoneoffset", each GetTimeZoneOffset([timezonecode])),
  JoinedProjectsWithTimezones = Table.NestedJoin(ExpandWorkHourTemplate, {"calendarid"}, AddTimeZoneOffset, {"calendarid"}, "timezonedetails", JoinKind.LeftOuter),
  ExpandTimezones = Table.ExpandTableColumn(JoinedProjectsWithTimezones, "timezonedetails", {"timezoneoffset"}, {"timezoneoffset"}),
   TaskTable = OData.Feed(BaseUrl, null, [ ODataVersion = 4, Implementation = "2.0", IncludeAnnotations="*"]),
  TaskTableJoinProjectTable = Table.NestedJoin(TaskTable, {"_msdyn_project_value"}, ExpandTimezones, {"msdyn_projectid"}, "timezonedetails", JoinKind.LeftOuter),
  TaskTableWithTimezoneOffset = Table.ExpandTableColumn(TaskTableJoinProjectTable, "timezonedetails", {"timezoneoffset"}, {"timezoneoffset"}),
  ConvertDateTimes = Table.FromRecords(
    Table.TransformRows(
      TaskTableWithTimezoneOffset,
      (r) =>
      Record.TransformFields(
        r,
        {
          {"msdyn_start", each ConvertDateTimeToProjectTimeZone(_, r[timezoneoffset])},
          {"msdyn_scheduledend", each ConvertDateTimeToProjectTimeZone(_, r[timezoneoffset])}
        }
      )
    ),
    Value.Type(TaskTableWithTimezoneOffset)
  ),
   Expand = Table.ExpandRecordColumn(ConvertDateTimes, "msdyn_projectbucket", {"msdyn_name", "msdyn_displayorder"}, {"sensei_bucket", "sensei_order"}),
  Transforms = Table.TransformColumns(Expand,
    {
      {"msdyn_duration", each if( _ = null) then 0 else _, type number },
      {"msdyn_projecttaskid", each Text.Upper(_), type text},
      {"_msdyn_parenttask_value", each Text.Upper(_), type text},
      {"msdyn_ismilestone", each Text.Lower(Logical.ToText(_)), type text},
      {"msdyn_summary", each Text.Lower(Logical.ToText(_)), type text },
      {"msdyn_subject", each Text.Start(Text.Trim(_), 256), type text },
      {"msdyn_progress", each Number.RoundUp(_ * 100), type number},
      {"sensei_bucket", each Text.Trim(_), type text}}),
  RenameColumns = Table.RenameColumns(Transforms, {{"msdyn_projecttaskid", "sensei_p4tw_taskid_key"}, {"_msdyn_project_value", "sensei_p4tw_projectid_key"}, {"msdyn_effort", "sensei_effort"}, {"msdyn_effortremaining", "sensei_effortremaining"}, {"msdyn_effortcompleted", "sensei_effortcompleted"}, {"msdyn_subject", "sensei_name"}, {"_msdyn_parenttask_value", "sensei_p4tw_parenttaskid_key"}, {"msdyn_ismilestone", "sensei_milestone"}, {"msdyn_summary", "sensei_summary"}, {"msdyn_duration", "sensei_duration"}, {"msdyn_progress", "sensei_percentcomplete"}, {"msdyn_start", "sensei_taskstartdate"}, {"msdyn_scheduledend", "sensei_taskfinishdate"}}),
  TableCopy = ...

DLP Policy Details

This integration will create/utilise the following Power Platform connections types:

This image shows connections in use by the dataflow

Downloads

Dataflows for the Project and Task Templates can be found on the Initial setup page.

FAQ

Percentage complete value is incorrect (1% appears as 100%)

If you notice that the task or project percentage complete value is showing as 100 times higher than expected (for example, 1% appears as 100%), this is likely due to incorrect field mapping for the task percentage complete field in your dataflow.

There are two fields available for mapping:

  • msdyn_progress: This field uses a multiplier of 1 (recommended).
  • sensei_msdyn_progress: This field uses a multiplier of 100.

Solution:
Ensure that you select to map to the msdyn_progress field in the dataflow configuration process to display the correct percentage complete value.

Edit this page