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 

 

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.