none
Combining three queries, filling in blanks RRS feed

  • Question

  • I'm trying to use Power Query to overcome a shortcoming of Project Online's delivered reporting capability.

    From Project Online, I pull three data sources via odata:

    Timesheets

    1. ResourceName
    2. EndDate
    3. TimesheetStatus

    TimePeriods

    1. EndDate

    Resources

    1. ResourceName
    2. CreatedDate
    3. EarliestAvailableDate

    The table Timesheets only holds entries from a timesheet which has been created, and thus have a status of In Progress, Approved, Rejected, etc. Notably missing from that table are all timesheets that have a status of Not Yet Created. The table TimePeriods, does have an exhaustive list of EndDates. So for a given EndDate from TimePeriods table, for any ResourceName from Resources table for which the pair doesn't exist in the Timesheets table, I need an entry in some new table, let's call it AllTimesheets, created for it with the value "Not Yet Created" for the TimesheetStatus... but only if that EndDate is greater than the EarliestAvailableDate in the Resources table for that ResourceName.

    So far, I am able to do the basics in Power Query, such as merges, appends, and UI-drive transformations like splits, replace ALL in column, etc., but this logic in Power Query is beyond me. I would be able to do these advanced things in Cognos, but am having trouble understand where to start with them here. Your pointers are much appreciated.


    Ian

    Tuesday, July 7, 2015 7:41 PM

Answers

  • Hi Ian,

    have a look if this one works for you:

    let
        LoadTimesheets = Excel.CurrentWorkbook(){[Name="Timesheets"]}[Content],
        LoadTimePeriods= Excel.CurrentWorkbook(){[Name="TimePeriods"]}[Content],
        TimePeriodP = Table.AddColumn(LoadTimePeriods, "Binder", each 1),

        LoadResources = Excel.CurrentWorkbook(){[Name="Resources"]}[Content],
        LookupTimesheets = Table.NestedJoin(LoadResources,{"ResourceName"},LoadTimesheets,{"ResourceName"},"NewColumn"),
        ExpandTimesheets = Table.ExpandTableColumn(LookupTimesheets, "NewColumn", {"ResourceName"}, {"ResourceName.1"}),
        FilterOnNonTimesheetItems = Table.SelectRows(ExpandTimesheets, each ([ResourceName.1] = null)),
        PrepareForCrossjoin = Table.AddColumn(FilterOnNonTimesheetItems, "Binder", each 1),
        CrossjoinTimePeriods = Table.NestedJoin(PrepareForCrossjoin,{"Binder"},TimePeriodP,{"Binder"},"NewColumn"),
        ShowTimePeriods = Table.ExpandTableColumn(CrossjoinTimePeriods, "NewColumn", {"EndDate"}, {"EndDate"}),
        CreateFilterColumn = Table.AddColumn(ShowTimePeriods, "NotYetCreated", each if [EndDate] > [EarliestAvailableDate] then "in" else "out"),
        ApplyFilter = Table.SelectRows(CreateFilterColumn, each ([NotYetCreated] = "in")),
        RemoveDuplicates = Table.Distinct(ApplyFilter, {"ResourceName"})
    in
        RemoveDuplicates


    Imke


    Wednesday, July 8, 2015 5:58 PM
    Moderator
  • Thanks very much Imke. At first your reply really confused me because I hadn't see in power query where to enter that m code.... newbie here.

    In the end, here's what I came up with:

    4 queries

    1) ResourcesTimephasedDataSet

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/ResourceTimephasedDataSet?$select=ResourceId,TimeByDay,BaseCapacity&$filter=BaseCapacity gt 0"),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.StartOfWeek([TimeByDay], Day.Monday)),
        #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "StartOfWeekWcapacity"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"TimeByDay", "BaseCapacity"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"StartOfWeekWcapacity", type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [StartOfWeekWcapacity] < DateTime.Date(DateTime.LocalNow()))    
    in
        #"Filtered Rows"

    2) Timesheets

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/Timesheets?$select= TimesheetStatusId,StatusDescription,TimesheetOwnerId,StartDate"),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}})
    in
        #"Changed Type"
     

    3) Resources (I have a custom enterprise field called "employmenttype" included. I also wanted to display only the child department of Resource Departments, so there's some splits and removes included.

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/Resources?$select=ResourceId,ResourceName,ResourceTimesheetManageId,ResourceDepartments,EmploymentType&$filter= ResourceType eq 2 and ResourceIsGeneric ne true"),
        #"Split Column by Delimiter" = Table.SplitColumn(Source,"ResourceDepartments",Splitter.SplitTextByEachDelimiter({"."}, null, true),{"ResourceDepartments.1", "ResourceDepartments.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ResourceDepartments.1", type text}, {"ResourceDepartments.2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ResourceDepartments.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ResourceDepartments.2", "ResourceDepartments"}}),
        #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"ResourceTimesheetManageId"},#"Renamed Columns",{"ResourceId"},"NewColumn"),
        #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"ResourceName"}, {"NewColumn.ResourceName"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Expanded NewColumn1",{{"NewColumn.ResourceName", "TimesheetManager"}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"ResourceTimesheetManageId"})
    in
        #"Removed Columns1"

    4) Bringing all those queries together, ALL_Timesheets. It includes two columns for status... one detailed, and the other I'm calling summary which groups into approved, submitted, and not submitted (which includes anything not of the first two statuses. This would mean that if you don't force a timesheet manager and have two or more possible approval steps that "acceptable" status would be included in "not submitted" in this report, but you could change that to be whatever you need.)

    let
        Source = Table.NestedJoin(ResourceTimephasedDataSet,{"ResourceId", "StartOfWeekWcapacity"},Timesheets,{"TimesheetOwnerId", "StartDate"},"NewColumn"),
        #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"StatusDescription"}, {"NewColumn.StatusDescription"}),
        #"Replaced Value" = Table.ReplaceValue(#"Expanded NewColumn",null,"Not Yet Created",Replacer.ReplaceValue,{"NewColumn.StatusDescription"}),
        #"Merged Queries" = Table.NestedJoin(#"Replaced Value",{"ResourceId"},Resources,{"ResourceId"},"NewColumn"),
        #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}, {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn2",{"ResourceId"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"StartOfWeekWcapacity", "WeekStarting"}, {"NewColumn.StatusDescription", "Status"}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ResourceDepartments", "TimesheetManager", "ResourceName", "WeekStarting", "Status"}),
        #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"ResourceDepartments", Order.Ascending}, {"TimesheetManager", Order.Ascending}, {"EmploymentType", Order.Ascending}, {"ResourceName", Order.Ascending}, {"WeekStarting", Order.Ascending}}),
        #"Added Custom" = Table.AddColumn(#"Sorted Rows", "SummaryStatus", each if [Status]="Approved" then [Status] else if [Status]="Submitted" then [Status] else "Not Submitted")
    in
        #"Added Custom"

    For the first three queries, load them to "Connection only", and the fourth, load to table and data model. I'm doing two tabs, one for the table, and one for a power view report.


    Ian

    • Marked as answer by Ian Bruckner Thursday, July 16, 2015 3:51 PM
    Thursday, July 16, 2015 3:50 PM

All replies

  • Or, vote for my feature request for Microsoft to handle this natively within Project Online.

    http://office365.uservoice.com/forums/264636-general/suggestions/8750512-project-online-reporting-include-not-yet-created

    Although, I would still be very interested to learn how to handle this using Power Query.


    Ian


    Tuesday, July 7, 2015 8:05 PM
  • Hi Ian,

    have a look if this one works for you:

    let
        LoadTimesheets = Excel.CurrentWorkbook(){[Name="Timesheets"]}[Content],
        LoadTimePeriods= Excel.CurrentWorkbook(){[Name="TimePeriods"]}[Content],
        TimePeriodP = Table.AddColumn(LoadTimePeriods, "Binder", each 1),

        LoadResources = Excel.CurrentWorkbook(){[Name="Resources"]}[Content],
        LookupTimesheets = Table.NestedJoin(LoadResources,{"ResourceName"},LoadTimesheets,{"ResourceName"},"NewColumn"),
        ExpandTimesheets = Table.ExpandTableColumn(LookupTimesheets, "NewColumn", {"ResourceName"}, {"ResourceName.1"}),
        FilterOnNonTimesheetItems = Table.SelectRows(ExpandTimesheets, each ([ResourceName.1] = null)),
        PrepareForCrossjoin = Table.AddColumn(FilterOnNonTimesheetItems, "Binder", each 1),
        CrossjoinTimePeriods = Table.NestedJoin(PrepareForCrossjoin,{"Binder"},TimePeriodP,{"Binder"},"NewColumn"),
        ShowTimePeriods = Table.ExpandTableColumn(CrossjoinTimePeriods, "NewColumn", {"EndDate"}, {"EndDate"}),
        CreateFilterColumn = Table.AddColumn(ShowTimePeriods, "NotYetCreated", each if [EndDate] > [EarliestAvailableDate] then "in" else "out"),
        ApplyFilter = Table.SelectRows(CreateFilterColumn, each ([NotYetCreated] = "in")),
        RemoveDuplicates = Table.Distinct(ApplyFilter, {"ResourceName"})
    in
        RemoveDuplicates


    Imke


    Wednesday, July 8, 2015 5:58 PM
    Moderator
  • Thanks very much Imke. At first your reply really confused me because I hadn't see in power query where to enter that m code.... newbie here.

    In the end, here's what I came up with:

    4 queries

    1) ResourcesTimephasedDataSet

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/ResourceTimephasedDataSet?$select=ResourceId,TimeByDay,BaseCapacity&$filter=BaseCapacity gt 0"),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.StartOfWeek([TimeByDay], Day.Monday)),
        #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "StartOfWeekWcapacity"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"TimeByDay", "BaseCapacity"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"StartOfWeekWcapacity", type date}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [StartOfWeekWcapacity] < DateTime.Date(DateTime.LocalNow()))    
    in
        #"Filtered Rows"

    2) Timesheets

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/Timesheets?$select= TimesheetStatusId,StatusDescription,TimesheetOwnerId,StartDate"),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}})
    in
        #"Changed Type"
     

    3) Resources (I have a custom enterprise field called "employmenttype" included. I also wanted to display only the child department of Resource Departments, so there's some splits and removes included.

    let
        Source = OData.Feed("https://<sitename>/sites/pwa/_api/ProjectData/Resources?$select=ResourceId,ResourceName,ResourceTimesheetManageId,ResourceDepartments,EmploymentType&$filter= ResourceType eq 2 and ResourceIsGeneric ne true"),
        #"Split Column by Delimiter" = Table.SplitColumn(Source,"ResourceDepartments",Splitter.SplitTextByEachDelimiter({"."}, null, true),{"ResourceDepartments.1", "ResourceDepartments.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ResourceDepartments.1", type text}, {"ResourceDepartments.2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ResourceDepartments.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ResourceDepartments.2", "ResourceDepartments"}}),
        #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"ResourceTimesheetManageId"},#"Renamed Columns",{"ResourceId"},"NewColumn"),
        #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"ResourceName"}, {"NewColumn.ResourceName"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Expanded NewColumn1",{{"NewColumn.ResourceName", "TimesheetManager"}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"ResourceTimesheetManageId"})
    in
        #"Removed Columns1"

    4) Bringing all those queries together, ALL_Timesheets. It includes two columns for status... one detailed, and the other I'm calling summary which groups into approved, submitted, and not submitted (which includes anything not of the first two statuses. This would mean that if you don't force a timesheet manager and have two or more possible approval steps that "acceptable" status would be included in "not submitted" in this report, but you could change that to be whatever you need.)

    let
        Source = Table.NestedJoin(ResourceTimephasedDataSet,{"ResourceId", "StartOfWeekWcapacity"},Timesheets,{"TimesheetOwnerId", "StartDate"},"NewColumn"),
        #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"StatusDescription"}, {"NewColumn.StatusDescription"}),
        #"Replaced Value" = Table.ReplaceValue(#"Expanded NewColumn",null,"Not Yet Created",Replacer.ReplaceValue,{"NewColumn.StatusDescription"}),
        #"Merged Queries" = Table.NestedJoin(#"Replaced Value",{"ResourceId"},Resources,{"ResourceId"},"NewColumn"),
        #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}, {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn2",{"ResourceId"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"StartOfWeekWcapacity", "WeekStarting"}, {"NewColumn.StatusDescription", "Status"}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ResourceDepartments", "TimesheetManager", "ResourceName", "WeekStarting", "Status"}),
        #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"ResourceDepartments", Order.Ascending}, {"TimesheetManager", Order.Ascending}, {"EmploymentType", Order.Ascending}, {"ResourceName", Order.Ascending}, {"WeekStarting", Order.Ascending}}),
        #"Added Custom" = Table.AddColumn(#"Sorted Rows", "SummaryStatus", each if [Status]="Approved" then [Status] else if [Status]="Submitted" then [Status] else "Not Submitted")
    in
        #"Added Custom"

    For the first three queries, load them to "Connection only", and the fourth, load to table and data model. I'm doing two tabs, one for the table, and one for a power view report.


    Ian

    • Marked as answer by Ian Bruckner Thursday, July 16, 2015 3:51 PM
    Thursday, July 16, 2015 3:50 PM