Extracting Local Task Custom Fields from Project Server with Power Query
A complete Power Query solution for extracting local task custom fields from the ProjectServer API, resolving internal field names and lookup table entries into a clean, pivoted table ready for reporting in Excel or Power BI.
Introduction: Leveraging the ProjectServer API and Power Query for Custom Field Access (Part 3 of 3)
This article is the third installment in a three-part series exploring how to harness Microsoft Project Server data using Power Query. In the first article, Accessing the ProjectServer API with Power Query, we introduced the basics of connecting to the ProjectServer API and navigating its structure. The second piece, Extracting Project Online Task Data, built on that foundation by demonstrating how the ProjectServer API returns task level data. Now, we conclude the series by tackling a critical challenge: accessing and transforming local custom fields for all tasks in a Project.
When extracting project data, two primary APIs are available: the ProjectData API and the ProjectServer API. While the ProjectData API offers a robust OData endpoint for standardized project information, it falls short when retrieving local custom fields, which are only accessible through the ProjectServer API. This JSON-based interface includes both standard fields and instance-specific custom fields, making it essential for comprehensive data retrieval. However, its raw output - a complex, nested JSON structure - requires significant transformation to be report-ready. Enter Power Query, Microsoft’s powerful data transformation tool, which excels at parsing JSON, filtering data, and reshaping it into clean tables. With its M language and integration with Excel and Power BI, Power Query is the perfect companion for unlocking the full potential of Project Server data, as we’ll demonstrate in this final article.
Overview: Extracting Local Task Custom Fields with This Query
This Power Query script is designed to address the challenge of retrieving and transforming local task custom field information from the ProjectServer API, a task the ProjectData API cannot accomplish due to its exclusion of instance-specific custom fields. The query begins by fetching all task data for a specified project using the ProjectServer API, which returns a JSON response containing both standard fields and local custom fields - identifiable by their LocalCustom_Published_x005f_ prefix. It then systematically processes this raw data to produce a clean, pivoted table where each row represents a task, and columns include both selected standard fields and fully resolved custom fields, complete with human-readable names, lookup values, and descriptions.
The process unfolds in several key steps. First, it filters out unwanted standard fields using a predefined exclusion list, retaining only the data relevant to the user’s needs. Next, it identifies and isolates custom fields, querying the API again to retrieve their metadata - such as friendly names and associated lookup tables. These custom fields are then enriched by replacing cryptic internal references with meaningful values and appending descriptions where applicable, ensuring the output is both comprehensive and interpretable. Finally, the data is pivoted into a task-centric table and typed for reporting, delivering a polished dataset ready for analysis in tools like Excel or Power BI.
This approach offers distinct advantages over using the ProjectData API. Unlike the ProjectData API, which provides a standardized but limited dataset excluding local custom fields, this query taps directly into the ProjectServer API’s ability to surface instance-specific attributes critical for tailored project management. It overcomes the JSON complexity through Power Query’s robust transformation engine, automating the extraction and refinement process without requiring manual coding or external tools. The result is a flexible, repeatable solution that not only accesses unique custom field data but also enhances its usability - saving time, reducing errors, and empowering organizations to leverage their full Project Server investment for precise, context-driven insights.
Step-by-Step Breakdown of the Query
Below is a detailed walkthrough of each step in the M query, including the code and a narrative explaining its purpose. Some of the steps have been shortened for ease of reading, but the full query text is included at the end of this article.
1. ExcludeFieldsList
This step defines a list of 151 standard Project Server API fields to exclude, keeping only uncommented fields (e.g., ActualDuration, BaselineFinish) and excluding related objects (e.g., __metadata). It reduces the dataset to relevant fields, ensuring the final table focuses on desired data. The order is important and must match the API response for accurate column header assignment later.
ExcludeFieldsList = {
// Related objects from standard api call, this related data is not required
"__metadata",
... ,
"TaskPlanLink",
// Standard Fields, comment out the ones you want to keep
"ActualCostWorkPerformed",
// "ActualDuration",
"ActualDurationMilliseconds",
... ,
"Work",
"WorkMilliseconds",
"WorkTimeSpan"
},
2. AllTasks
Fetches task data from the ProjectServer API as a JSON response using PWASiteUrl and ProjectUID, extracting the results node. This retrieves the raw task data, forming the foundation for all transformations.
AllTasks = Json.Document(
Web.Contents(
PWASiteUrl & "/_api/ProjectServer/Projects('" & ProjectUID & "')",
[
RelativePath = "/Tasks",
Headers = [
#"Content-Type" = "application/json; charset=UTF-8",
#"Accept" = "application/json;odata=verbose"
]
]
)
)[d][results],
3. ConvertToTable
Converts the JSON list of task records into a table with a single column TaskFields. This transforms the list into a tabular format, enabling further manipulation.
ConvertToTable = Table.FromList(AllTasks, Splitter.SplitByNothing(), {"TaskFields"}, null, ExtraValues.Error),
4. TaskGUID
Adds a Task GUID column by extracting the Id field from each TaskFields record. This provides a unique identifier for each task, essential for tracking and pivoting.
TaskGUID = Table.AddColumn(ConvertToTable, "Task GUID", each [TaskFields][Id], type text),
5. KeyValues
Adds a KeyValues column, converting each TaskFields record into a nested table of field names and values. This prepares the data for expansion into key-value pairs.
KeyValues = Table.AddColumn(TaskGUID,"KeyValues", each Record.ToTable(_[TaskFields])),
6. #Expanded KeyValues
Expands KeyValues into Designator (field name) and RawValue (field value) columns. This flattens the structure, making fields accessible for filtering.
#"Expanded KeyValues" = Table.ExpandTableColumn(KeyValues, "KeyValues", {"Name", "Value"}, {"Designator", "RawValue"}),
7. #Filtered Rows
Filters out rows where Designator matches ExcludeFieldsList. This excludes unwanted standard fields while retaining custom fields, tailoring the dataset.
#"Filtered Rows" = Table.SelectRows(#"Expanded KeyValues", each not List.Contains(ExcludeFieldsList,[Designator])),
8. RemoveTaskFields
Removes the original TaskFields column. This cleans up the table after expansion, removing redundant data.
RemoveTaskFields = Table.RemoveColumns(#"Filtered Rows",{"TaskFields"}),
9. CustomFields
Creates a temporary table of unique custom fields, fetching their metadata (e.g., Name, FieldType) from the API. This enriches custom fields with readable names and lookup details.
CustomFields = let
FieldsColumn = Table.SelectColumns(RemoveTaskFields,{"Designator"}),
FilteredList = Table.SelectRows(FieldsColumn, each Text.Contains([Designator], "x005f_")),
UniqueCustomFields = Table.Distinct(FilteredList),
// FieldType refers to the CustomField.Type Enum in Namespace:Microsoft.Office.Project.Server.Library
GetFieldInfo = Table.AddColumn(UniqueCustomFields, "LookupValues", each Json.Document(
Web.Contents(
PWASiteUrl & "/_api/ProjectServer",
[
RelativePath = "/CustomFields('" & Text.Replace([Designator],"x005f_","") & "')",
Query = [
#"$select" = "Id,InternalName, Name, FieldType,LookupEntries/InternalName,LookupEntries/FullValue,LookupEntries/Description",
#"$expand" = "LookupEntries"
],
Headers = [
#"Content-Type" = "application/json; charset=UTF-8",
#"Accept" = "application/json;odata=verbose"
]
]
)
)[d]),
// Here we get the lookup table entry internal names and values where they exist
ExpandFieldInfo = Table.ExpandRecordColumn(GetFieldInfo, "LookupValues", {"LookupEntries", "FieldType", "Id", "Name"}, {"Choices", "FieldType", "Id", "CustomField"}),
LabelFieldTypes= Table.TransformColumns(ExpandFieldInfo,
{"FieldType", each
if _=4 then "Date" else
if _=6 then "Duration" else
if _=9 then "Cost" else
if _=15 then "Number" else
if _=17 then "Flag" else
if _=21 then "Text" else
if _=27 then "FinishDate" else
"Unknown", type text}
),
CleanColumns = Table.TransformColumnTypes(LabelFieldTypes,{
{"Designator", type text},
{"Id", type text},
{"CustomField", type text}
}),
ExpandChoices = Table.ExpandRecordColumn(CleanColumns, "Choices", {"results"}, {"ChoiceLists"})
in
ExpandChoices,
10. LookupEntries
Creates a temporary table of lookup entries from CustomFields. This isolates lookup values for custom fields, enabling value replacement.
LookupEntries = let
ChoiceLists = Table.SelectColumns(CustomFields,{"ChoiceLists"}),
OnlyLookups = Table.SelectRows(ChoiceLists, each [ChoiceLists] <> null),
AllRecords = Table.ExpandListColumn(OnlyLookups, "ChoiceLists"),
AllChoices = Table.ExpandRecordColumn(AllRecords, "ChoiceLists", {"Description", "FullValue", "InternalName"}, {"Description", "FullValue", "InternalName"})
in
AllChoices,
11. MarkCustom
Flags fields with x005f_ as custom (true). This differentiates custom fields for special handling.
MarkCustom = Table.AddColumn(RemoveTaskFields, "IsCustom", each
if Text.Contains([Designator], "x005f_") then true else false
),
12. GetFieldNames
Replaces custom field Designator values with CustomField names from CustomFields. This provides readable names for custom fields.
GetFieldNames = Table.AddColumn(MarkCustom, "FieldName", each
if [IsCustom] then
Table.SelectRows(
CustomFields,
(lookupFieldName)=>
lookupFieldName[Designator] = [Designator]
){0}[CustomField] else [Designator]
),
13. GetLookupValues
Replaces custom field RawValue records with FullValue from LookupEntries. This converts lookup references into meaningful values.
GetLookupValues = Table.AddColumn(GetFieldNames, "FinalValue", each
if [IsCustom] and Type.Is(Value.Type([RawValue]), type record) then
Table.SelectRows(
LookupEntries,
(entryValue)=>
entryValue[InternalName] = List.First([RawValue][results])
){0}[FullValue]
else [RawValue]
),
14. CustomDescriptions
Creates a table for custom fields with lookup descriptions, appending -Description to FieldName. This adds descriptive context.
CustomDescriptions = let
RelevantRows = Table.SelectRows(
GetLookupValues,
each [IsCustom] and Type.Is(Value.Type([RawValue]), type record)
),
NewNames = Table.TransformColumns(RelevantRows,{
"FieldName", each _ & "-Description", type text
}),
RemoveValues = Table.RemoveColumns(NewNames,"FinalValue"),
NewValues = Table.AddColumn(RemoveValues, "FinalValue", each
Table.SelectRows(
LookupEntries,
(entryValue)=>
entryValue[InternalName] = List.First([RawValue][results])
){0}[Description]
),
RemoveNulls = Table.SelectRows(NewValues, each [FinalValue] <> null)
in
RemoveNulls,
15. CombineFieldsAndDescriptions
Combines GetLookupValues and CustomDescriptions. This integrates lookup descriptions into the main dataset.
CombineFieldsAndDescriptions =Table.Combine({GetLookupValues,CustomDescriptions}),
16. RemoveWorkingColumns
Keeps only Task GUID, FieldName, and FinalValue. This prepares the table for pivoting.
RemoveWorkingColumns = Table.SelectColumns(CombineFieldsAndDescriptions,{"Task GUID","FieldName","FinalValue"}),
17. MakeTaskTable
Pivots the table, turning FieldName into columns and FinalValue into cell values. This creates a task-centric table.
MakeTaskTable = Table.Pivot(RemoveWorkingColumns, List.Distinct(RemoveWorkingColumns[#"FieldName"]),"FieldName","FinalValue"),
18. Transform1
Applies basic type transformations to standard columns. This ensures correct data types for reporting.
Transform1 = Table.TransformColumnTypes(MakeTaskTable,{
{"Finish", type datetime},
{"Start", type datetime},
{"IsActive", type logical},
{"IsCritical", type logical},
{"IsMilestone", type logical},
{"IsSummary", type logical},
{"Name", type text},
{"OutlineLevel", Int64.Type},
{"OutlinePosition", type text},
{"TaskType", Int64.Type} // 0 = Fixed Units, 1 = Fixed Duration, 2 = Fixed Work
}),
19. Transform2
Applies complex transformations (e.g., nulling invalid dates, scaling percentages). This refines data types for usability.
Transform2 = Table.TransformColumns(Transform1, {
{"BaselineStart", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"BaselineFinish", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"ActualStart", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"ActualFinish", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"Deadline", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"ActualDuration",each Number.FromText(Text.Remove(_,"d")), type number},
{"BaselineDuration",each Number.FromText(Text.Remove(_,"d")), type number},
{"Duration",each Number.FromText(Text.Remove(_,"d")), type number},
{"RemainingDuration",each Number.FromText(Text.Remove(_,"d")), type number},
{"PercentPhysicalWorkComplete",each _/100,Percentage.Type},
{"PercentComplete",each _/100,Percentage.Type}
})
Summary and Complete Code Example
This article provides a comprehensive guide to extracting and transforming Project Server task data, with a focus on local custom fields, using Power Query and the ProjectServer API. Copy the full query below into Power Query Editor, adjust PWASiteUrl and ProjectUID, and customize the ExcludeFieldsList and transformations as needed for your environment!
let
/*
Create a JSON list of fields you want to exclude.
There are 151 standard fields in the Project Server API field list, exclude the ones you do not need.
Note that the order matters, and should match the order that the API uses when it returns
values. This is because we are using this list to set the column headers when we convert
the JSON response to a usable table in the "ResultsToTable" step below.
*/
ExcludeFieldsList = {
// Related objects from standard api call, this related data is not required
"__metadata",
"CustomFields",
"SubProject",
"Assignments",
"Calendar",
"EntityLinks",
"Parent",
"Predecessors",
"StatusManager",
"Successors",
"TaskPlanLink",
// Standard Fields, comment out the ones you want to keep
"ActualCostWorkPerformed",
// "ActualDuration",
"ActualDurationMilliseconds",
"ActualDurationTimeSpan",
"ActualOvertimeCost",
"ActualOvertimeWork",
"ActualOvertimeWorkMilliseconds",
"ActualOvertimeWorkTimeSpan",
"BaselineCost",
// "BaselineDuration",
"BaselineDurationMilliseconds",
"BaselineDurationTimeSpan",
// "BaselineFinish",
// "BaselineStart",
"BaselineWork",
"BaselineWorkMilliseconds",
"BaselineWorkTimeSpan",
"BudgetCost",
"BudgetedCostWorkPerformed",
"BudgetedCostWorkScheduled",
"Contact",
"CostPerformanceIndex",
"CostVariance",
"CostVarianceAtCompletion",
"CostVariancePercentage",
"Created",
"CurrentCostVariance",
"DurationVariance",
"DurationVarianceMilliseconds",
"DurationVarianceTimeSpan",
"EarliestFinish",
"EarliestStart",
"EstimateAtCompletion",
"ExternalProjectUid",
"ExternalTaskUid",
"FinishSlack",
"FinishSlackMilliseconds",
"FinishSlackTimeSpan",
"FinishVariance",
"FinishVarianceMilliseconds",
"FinishVarianceTimeSpan",
"FreeSlack",
"FreeSlackMilliseconds",
"FreeSlackTimeSpan",
"Id",
"IgnoreResourceCalendar",
// "IsCritical",
"IsDurationEstimate",
"IsExternalTask",
"IsOverAllocated",
"IsRecurring",
"IsRecurringSummary",
"IsRolledUp",
"IsSubProject",
"IsSubProjectReadOnly",
"IsSubProjectScheduledFromFinish",
// "IsSummary",
"LatestFinish",
"LatestStart",
"LevelingDelay",
"LevelingDelayMilliseconds",
"LevelingDelayTimeSpan",
"Modified",
"Notes",
// "OutlinePosition",
"OvertimeCost",
"OvertimeWork",
"OvertimeWorkMilliseconds",
"OvertimeWorkTimeSpan",
"PercentWorkComplete",
"PreLevelingFinish",
"PreLevelingStart",
"RegularWork",
"RegularWorkMilliseconds",
"RegularWorkTimeSpan",
"RemainingCost",
"RemainingOvertimeCost",
"RemainingOvertimeWork",
"RemainingOvertimeWorkMilliseconds",
"RemainingOvertimeWorkTimeSpan",
"RemainingWork",
"RemainingWorkMilliseconds",
"RemainingWorkTimeSpan",
"Resume",
"ScheduleCostVariance",
"ScheduledDuration",
"ScheduledDurationMilliseconds",
"ScheduledDurationTimeSpan",
"ScheduledFinish",
"ScheduledStart",
"SchedulePerformanceIndex",
"ScheduleVariancePercentage",
"StartSlack",
"StartSlackMilliseconds",
"StartSlackTimeSpan",
"StartVariance",
"StartVarianceMilliseconds",
"StartVarianceTimeSpan",
"Stop",
"ToCompletePerformanceIndex",
"TotalSlack",
"TotalSlackMilliseconds",
"TotalSlackTimeSpan",
"WorkBreakdownStructure",
"WorkVariance",
"WorkVarianceMilliseconds",
"WorkVarianceTimeSpan",
"ActualCost",
// "ActualFinish",
// "ActualStart",
"ActualWork",
"ActualWorkMilliseconds",
"ActualWorkTimeSpan",
"BudgetWork",
"BudgetWorkMilliseconds",
"BudgetWorkTimeSpan",
"Completion",
"ConstraintStartEnd",
"ConstraintType",
"Cost",
// "Deadline",
// "Duration",
"DurationMilliseconds",
"DurationTimeSpan",
// "Finish",
"FinishText",
"FixedCost",
"FixedCostAccrual",
// "IsActive",
"IsEffortDriven",
"IsLockedByManager",
"IsManual",
"IsMarked",
// "IsMilestone",
"LevelingAdjustsAssignments",
"LevelingCanSplit",
// "Name",
// "OutlineLevel",
// "PercentComplete",
// "PercentPhysicalWorkComplete",
"Priority",
// "RemainingDuration",
"RemainingDurationMilliseconds",
"RemainingDurationTimeSpan",
// "Start",
"StartText",
// "TaskType",
"UsePercentPhysicalWorkComplete",
"Work",
"WorkMilliseconds",
"WorkTimeSpan"
},
/*
Go get all the tasks for this project using the ProjectServer API.
There are two parameters used below:
PWASiteUrl = your project online site, for example "https://yourcompany.sharepoint.com/sites/pwa"
ProjectUID = the project GUID you want to pull from, for example "988d420-bb75-ef71-acfc-a2e374bb6a1"
You can set up parameters in your Power Query editor window or just hard code the values in this query.
*/
AllTasks = Json.Document(
Web.Contents(
PWASiteUrl & "/_api/ProjectServer/Projects('" & ProjectUID & "')",
[
RelativePath = "/Tasks",
Headers = [
#"Content-Type" = "application/json; charset=UTF-8",
#"Accept" = "application/json;odata=verbose"
]
]
)
)[d][results],
// The resulst come in as a list of JSON records. We need to turn that list into an M table and isolate the key value pairs of Name and Value for each field
ConvertToTable = Table.FromList(AllTasks, Splitter.SplitByNothing(), {"TaskFields"}, null, ExtraValues.Error),
// Add the TaskGUID column
TaskGUID = Table.AddColumn(ConvertToTable, "Task GUID", each [TaskFields][Id], type text),
// Convert the TaskFields records to tables
KeyValues = Table.AddColumn(TaskGUID,"KeyValues", each Record.ToTable(_[TaskFields])),
// Expand the task field table columns
#"Expanded KeyValues" = Table.ExpandTableColumn(KeyValues, "KeyValues", {"Name", "Value"}, {"Designator", "RawValue"}),
// With the field names available, now we can exclude the standard fields we don't want.
// Unfortunately, we can not use an include because that would leave out the custom fields! The ProjectServer API only returns custom fields actually used
// for that task, so we don't know in advance how many custom fields will be returned.
#"Filtered Rows" = Table.SelectRows(#"Expanded KeyValues", each not List.Contains(ExcludeFieldsList,[Designator])),
// Now we can remove the TaskFields colum as we have already expanded it to other columns
RemoveTaskFields = Table.RemoveColumns(#"Filtered Rows",{"TaskFields"}),
// Here we get a unique list of custom fields across all tasks so we can efficiently go get the field names and any lookup tables if required
CustomFields = let
FieldsColumn = Table.SelectColumns(RemoveTaskFields,{"Designator"}),
FilteredList = Table.SelectRows(FieldsColumn, each Text.Contains([Designator], "x005f_")),
UniqueCustomFields = Table.Distinct(FilteredList),
// FieldType refers to the CustomField.Type Enum in Namespace:Microsoft.Office.Project.Server.Library
GetFieldInfo = Table.AddColumn(UniqueCustomFields, "LookupValues", each Json.Document(
Web.Contents(
PWASiteUrl & "/_api/ProjectServer",
[
RelativePath = "/CustomFields('" & Text.Replace([Designator],"x005f_","") & "')",
Query = [
#"$select" = "Id,InternalName, Name, FieldType,LookupEntries/InternalName,LookupEntries/FullValue,LookupEntries/Description",
#"$expand" = "LookupEntries"
],
Headers = [
#"Content-Type" = "application/json; charset=UTF-8",
#"Accept" = "application/json;odata=verbose"
]
]
)
)[d]),
// Here we get the lookup table entry internal names and values where they exist
ExpandFieldInfo = Table.ExpandRecordColumn(GetFieldInfo, "LookupValues", {"LookupEntries", "FieldType", "Id", "Name"}, {"Choices", "FieldType", "Id", "CustomField"}),
LabelFieldTypes= Table.TransformColumns(ExpandFieldInfo,
{"FieldType", each
if _=4 then "Date" else
if _=6 then "Duration" else
if _=9 then "Cost" else
if _=15 then "Number" else
if _=17 then "Flag" else
if _=21 then "Text" else
if _=27 then "FinishDate" else
"Unknown", type text}
),
CleanColumns = Table.TransformColumnTypes(LabelFieldTypes,{
{"Designator", type text},
{"Id", type text},
{"CustomField", type text}
}),
ExpandChoices = Table.ExpandRecordColumn(CleanColumns, "Choices", {"results"}, {"ChoiceLists"})
in
ExpandChoices,
// This creates a seperate temporary table of only lookup entries and their associated values
LookupEntries = let
ChoiceLists = Table.SelectColumns(CustomFields,{"ChoiceLists"}),
OnlyLookups = Table.SelectRows(ChoiceLists, each [ChoiceLists] <> null),
AllRecords = Table.ExpandListColumn(OnlyLookups, "ChoiceLists"),
AllChoices = Table.ExpandRecordColumn(AllRecords, "ChoiceLists", {"Description", "FullValue", "InternalName"}, {"Description", "FullValue", "InternalName"})
in
AllChoices,
// Mark all fields in the main table as custom or not with a binary flag
MarkCustom = Table.AddColumn(RemoveTaskFields, "IsCustom", each
if Text.Contains([Designator], "x005f_") then true else false
),
// If the field is custom, get the custom FieldName from our first temporary table
GetFieldNames = Table.AddColumn(MarkCustom, "FieldName", each
if [IsCustom] then
Table.SelectRows(
CustomFields,
(lookupFieldName)=>
lookupFieldName[Designator] = [Designator]
){0}[CustomField] else [Designator]
),
// If the custom field has a lookup table entry, lookup the value from our LookupEntries list
GetLookupValues = Table.AddColumn(GetFieldNames, "FinalValue", each
if [IsCustom] and Type.Is(Value.Type([RawValue]), type record) then
Table.SelectRows(
LookupEntries,
(entryValue)=>
entryValue[InternalName] = List.First([RawValue][results])
){0}[FullValue]
else [RawValue]
),
// For lookup table entry values, we have to add one more row to the main table to include the value description from the lookup entry value for that field.
CustomDescriptions = let
RelevantRows = Table.SelectRows(
GetLookupValues,
each [IsCustom] and Type.Is(Value.Type([RawValue]), type record)
),
NewNames = Table.TransformColumns(RelevantRows,{
"FieldName", each _ & "-Description", type text
}),
RemoveValues = Table.RemoveColumns(NewNames,"FinalValue"),
NewValues = Table.AddColumn(RemoveValues, "FinalValue", each
Table.SelectRows(
LookupEntries,
(entryValue)=>
entryValue[InternalName] = List.First([RawValue][results])
){0}[Description]
),
RemoveNulls = Table.SelectRows(NewValues, each [FinalValue] <> null)
in
RemoveNulls,
CombineFieldsAndDescriptions =Table.Combine({GetLookupValues,CustomDescriptions}),
// Finally, remove working columns and pivot the table so the field names are columns and the tasks are rows
RemoveWorkingColumns = Table.SelectColumns(CombineFieldsAndDescriptions,{"Task GUID","FieldName","FinalValue"}),
MakeTaskTable = Table.Pivot(RemoveWorkingColumns, List.Distinct(RemoveWorkingColumns[#"FieldName"]),"FieldName","FinalValue"),
// Transform the column data types for use in reporting. Note these are for standard fields, you will have to add other transformations for your custom fields
Transform1 = Table.TransformColumnTypes(MakeTaskTable,{
{"Finish", type datetime},
{"Start", type datetime},
{"IsActive", type logical},
{"IsCritical", type logical},
{"IsMilestone", type logical},
{"IsSummary", type logical},
{"Name", type text},
{"OutlineLevel", Int64.Type},
{"OutlinePosition", type text},
{"TaskType", Int64.Type} // 0 = Fixed Units, 1 = Fixed Duration, 2 = Fixed Work
}),
// Transform2 is necessary because you cannot combine transformations that use the each iterator (i.e. complex transformations) with ones that do not.
// Again, these are only for standard fields. You will need to add your own for your custom fields.
Transform2 = Table.TransformColumns(Transform1, {
{"BaselineStart", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"BaselineFinish", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"ActualStart", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"ActualFinish", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"Deadline", each if Comparer.Equals(Comparer.Ordinal, _, "0001-01-01T00:00:00") then null else DateTime.From(_), type datetime},
{"ActualDuration",each Number.FromText(Text.Remove(_,"d")), type number},
{"BaselineDuration",each Number.FromText(Text.Remove(_,"d")), type number},
{"Duration",each Number.FromText(Text.Remove(_,"d")), type number},
{"RemainingDuration",each Number.FromText(Text.Remove(_,"d")), type number},
{"PercentPhysicalWorkComplete",each _/100,Percentage.Type},
{"PercentComplete",each _/100,Percentage.Type}
})
in
Transform2