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 FT...
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.
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.
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I appreciate your time and effort here. If I understand you correctly; what's critical to this approach is creating a key so it can relate to the original table that contains the project information. That creates a whole other challenge.

         

        When all is said and done, the approach may be to ask the data owner to restructure their data.