Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
vutruong91
Regular Visitor

Convert multiple column data into a column with multiple rows

Hi,

 

I currently have this data:


Capture.PNG

And I wish to have the following results:

 

Capture 1.PNG

 

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!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
GDUb
Advocate I
Advocate I

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! 

Hi

 

You should use the unpivot on the power query. 

 

That way there is no need to rewrite


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Zubair_Muhammad
Community Champion
Community Champion

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

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.