Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Unpivot

Hello all...

I would like to have Resource #, Resource Name, and Resource Hrs/Week columns instead of how the data is organized in the first table below.

I selected the other columns (Total # of FTE Resources and Total # of Contractor resources) and then unpivoted other columns and arrived at the second table below. Is anyone able to guide me on the next steps? 

 

 

Need Help
  • VasTg's avatar
    VasTg
    6 years ago

    Anonymous 

     

    I am not sure how to create the table for dynamic number of Resource # Name and Hrs/Week columns. Maybe someone out here could help with M query.

     

    If we know the columns are fixed, i could use DAX to create a new table and generate some kind of index as key.

     

    Dax:

     

    Table 2 = UNION(SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 1 Name],"Hrs/Week",'Table (3)'[Resource 1 Hrs/Week]),
    SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 2 Name],"Hrs/Week",'Table (3)'[Resource 2 Hrs/Week]),
    SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 3 Name],"Hrs/Week",'Table (3)'[Resource 3 Hrs/Week]),
    SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 4 Name],"Hrs/Week",'Table (3)'[Resource 4 Hrs/Week]))
     
     
    Input
     
    Output
     
    If this helps, mark it as solution.
    Kudos are good too.

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I thought I was clear regarding what I'm trying to accomplish, but to add more context, I'd like to be able to pull a "Resource" field in to a table instead of the "Resource #1, Resource #2, etc. fields.

      The link you sent just shows how to perform the steps that I already took.

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    Anonymous 

     

    Where is the Resource # column? Are you refering to  # of FTE or # of Contractor columns?

     

    Do you only have 4 sets of Resource # Name and Resource Hrs/Week columns?

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      That's the issue I'm having with this data. There are no separate Resource # columns. The FTE, Contractor, and all other columns are organized how we would hope they would be. The Resource columns are not.

      I want to be able to add a Project name page level filter, for example, and then have a table that lists all of the resources and number of hours they're working on that project instead of having to create a table with the Resource #1 Name, Resource #1 Hrs/Week, Resource #2 Name, Resource #2 Hrs/Week, etc., fields pulled in. 

      • VasTg's avatar
        VasTg
        Memorable Member
        6 years ago

        Anonymous 

         

        I am not sure how to create the table for dynamic number of Resource # Name and Hrs/Week columns. Maybe someone out here could help with M query.

         

        If we know the columns are fixed, i could use DAX to create a new table and generate some kind of index as key.

         

        Dax:

         

        Table 2 = UNION(SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 1 Name],"Hrs/Week",'Table (3)'[Resource 1 Hrs/Week]),
        SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 2 Name],"Hrs/Week",'Table (3)'[Resource 2 Hrs/Week]),
        SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 3 Name],"Hrs/Week",'Table (3)'[Resource 3 Hrs/Week]),
        SELECTCOLUMNS('Table (3)',"Resource",'Table (3)'[Resource 4 Name],"Hrs/Week",'Table (3)'[Resource 4 Hrs/Week]))
         
         
        Input
         
        Output
         
        If this helps, mark it as solution.
        Kudos are good too.