Convert multiple column data into a column with multiple rows

[vutruong91]

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!

[GDUb]

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]

Hi

You should use the unpivot on the power query. 

That way there is no need to rewrite

[MFelix · Accepted solution]

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

[Zubair_Muhammad]

@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]
    )
)