Forum Discussion

vutruong91's avatar
vutruong91
Regular Visitor
7 years ago
Solved

Convert multiple column data into a column with multiple rows

Hi,

 

I currently have this data:


And I wish to have the following results:

 

 

How can I create this using the "New Table" function? I might also create a new excel sheet with the "Categories". How then can I utilize DAX 

 

Thanks in advance!

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi vutruong91 ,

     

    The easist way is on the query editor, select the columns Project and choose unpivot columns. This is my recommend way.

     

    However you can also do this in DAX you can do the following formula:

     

    DAX_Table =
UNION (
    SELECTCOLUMNS (
        Projects_Dax;
        "DESCRIPTION"; Projects_Dax[Description];
        "VALUE"; Projects_Dax[Project A];
        "FOR"; "Project A"
    );
    SELECTCOLUMNS (
        Projects_Dax;
        "DESCRIPTION"; Projects_Dax[Description];
        "VALUE"; Projects_Dax[Project B];
        "FOR"; "Project B"
    );
    SELECTCOLUMNS (
        Projects_Dax;
        "DESCRIPTION"; Projects_Dax[Description];
        "VALUE"; Projects_Dax[Project C];
        "FOR"; "Project C"
    );
    SELECTCOLUMNS (
        Projects_Dax;
        "DESCRIPTION"; Projects_Dax[Description];
        "VALUE"; Projects_Dax[Project D];
        "FOR"; "Project D"
    )
)

    Check attach the PBIX file with both options.

     

    Regards,

    MFelix

     

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    vutruong91 

     

    One way of manual unpivoting using DAX could be to select each column individually and then append it beneath other column

     

    Table =
UNION (
    SELECTCOLUMNS (
        Table1,
        "For", "Project A",
        "Description", [Description],
        "Value", [Project A]
    ),
    SELECTCOLUMNS (
        Table1,
        "For", "Project B",
        "Description", [Description],
        "Value", [Project B]
    )
)

     

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi vutruong91 ,

     

    The easist way is on the query editor, select the columns Project and choose unpivot columns. This is my recommend way.

     

    However you can also do this in DAX you can do the following formula:

     

    DAX_Table =
UNION (
    SELECTCOLUMNS (
        Projects_Dax;
        "DESCRIPTION"; Projects_Dax[Description];
        "VALUE"; Projects_Dax[Project A];
        "FOR"; "Project A"
    );
    SELECTCOLUMNS (
        Projects_Dax;
        "DESCRIPTION"; Projects_Dax[Description];
        "VALUE"; Projects_Dax[Project B];
        "FOR"; "Project B"
    );
    SELECTCOLUMNS (
        Projects_Dax;
        "DESCRIPTION"; Projects_Dax[Description];
        "VALUE"; Projects_Dax[Project C];
        "FOR"; "Project C"
    );
    SELECTCOLUMNS (
        Projects_Dax;
        "DESCRIPTION"; Projects_Dax[Description];
        "VALUE"; Projects_Dax[Project D];
        "FOR"; "Project D"
    )
)

    Check attach the PBIX file with both options.

     

    Regards,

    MFelix

     

  • GDUb's avatar
    GDUb
    Advocate I
    2 years ago

    Is there a way to do this for an unknown / changing number of project values, so that formulas don't have to be rewritten every time a new project value is added to the data table? Thanks! 

    • MFelix's avatar
      MFelix
      Super User
      2 years ago

      Hi

       

      You should use the unpivot on the power query. 

       

      That way there is no need to rewrite