Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIs 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em Português
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] ) )
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
74 | |
57 | |
38 | |
33 |
User | Count |
---|---|
70 | |
63 | |
55 | |
48 | |
46 |