none
Power Query lookup with filtering RRS feed

  • Question

  • I'm looking for the advice how to put Timesheet manager name into new column based on ResourceTimesheetManageId and value form ResourceName using ResourceID mapping. As a workaround I replaced those ID manually but for the long term I need to automate this. (I'm using Project Online)

    Monday, February 1, 2016 4:09 PM

Answers

  • This is a different approach that also works if there are duplicates in your table:

    TimeSheetManagerName = Table.ExpandListColumn(Table.AddColumn(NameOfThePreviousStep, "TimeSheetManagerName", each let ResourceTimesheetManageId=[ResourceTimesheetManageId] in List.Distinct(Table.SelectRows(Source, each [ResourceID ]= ResourceTimesheetManageId)[RessourceName])), "TimeSheetManagerName")

    This will add a column "TimeSheetManagerName" to your table. You will have to fill in the NameOfThePreviousStep


    Imke Feldmann TheBIccountant.com


    Monday, February 1, 2016 5:35 PM
    Moderator

All replies

  • You can merge the query with itself. This is Power Query terminology for a join. Simply select the fields that must be equal, and then you'll get a new field, which contains a table. You can expand out that table to just the new field you need.

    Check out the Power BI User Group of Philadelphia.
    Our next meeting is February 2 in Malvern, PA.

    Monday, February 1, 2016 4:45 PM
  • This is a different approach that also works if there are duplicates in your table:

    TimeSheetManagerName = Table.ExpandListColumn(Table.AddColumn(NameOfThePreviousStep, "TimeSheetManagerName", each let ResourceTimesheetManageId=[ResourceTimesheetManageId] in List.Distinct(Table.SelectRows(Source, each [ResourceID ]= ResourceTimesheetManageId)[RessourceName])), "TimeSheetManagerName")

    This will add a column "TimeSheetManagerName" to your table. You will have to fill in the NameOfThePreviousStep


    Imke Feldmann TheBIccountant.com


    Monday, February 1, 2016 5:35 PM
    Moderator
  • Thanks a lot! I will give it a try tomorrow! 
    Monday, February 1, 2016 10:01 PM
  • Looks OK at first sight,

    only thing that needs to be corrected is to close with:

    in

    TimeSheetManagerName

    instead of: #"Added Custom"


    Imke Feldmann TheBIccountant.com

    Tuesday, February 2, 2016 12:58 PM
    Moderator
  • This is my working solution for Excel 2016 

    let
        Source = OData.Feed("https://<your site>.sharepoint.com/sites/pwa/_api/projectdata"),
        Resources_table = Source{[Name="Resources",Signature="table"]}[Data],
        TimeSheetManagerName = Table.ExpandListColumn(Table.AddColumn(Resources_table, "TimeSheetManagerName", each 
     let 
      ResourceTimesheetManageId=[ResourceTimesheetManageId]
     in
      List.Distinct(Table.SelectRows(Resources_table, each [ResourceId]=ResourceTimesheetManageId)[ResourceName])), "TimeSheetManagerName")
    in
        TimeSheetManagerName

    Tuesday, February 2, 2016 5:58 PM