Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Excel has a TRANSPOSE function, DAX does not. After creating these DAX quick measures I think I know why. And yes, I realize that this is a single click in Query Editor but if you abosolutely have to have a dynamic table/matrix transpose, here you go. I have included transpositions for everything from a 1 row, 2 column table to a 4 row, 4 column table. Additional transpositions can be created based upon the pattern presented here.
For reference, here is the 4x4 transposition.
Transposed4x4 = // We must build our row one column at a time, each varialbe is named __rowRC were R=row and C=column // We start by building our first row "set" using all of the values from the first Column of the original table // There will be as many of these row sets as there are columns in the original table. // There will be as many values in each row set as there are rows in the original table. VAR __row1 = FILTER('Table4x4',[Index]=1) VAR __row2 = FILTER('Table4x4',[Index]=2) VAR __row3 = FILTER('Table4x4',[Index]=3) VAR __row4 = FILTER('Table4x4',[Index]=4) VAR __row11 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row1, // We first filter our table to a single row [Index], // Group by row "1",MAXX(CURRENTGROUP(),[Column1]) // Get the MAX of the first Column and put it in a column named "1" ), "Row","1" // Add a row identifier ), "Row",[Row], // Select only our Row and 1 columns "1",[1] ) VAR __row12 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row2, // We first filter our table to a single row [Index], // Group by row "2",MAXX(CURRENTGROUP(),[Column1]) // Get the MAX of the first Column and put it in a column named "2" ), "Row","1" // Add a row identifier ), "Row",[Row], // Select only our Row and 2 columns "2",[2] ) VAR __row13 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row3, // We first filter our table to a single row [Index], // Group by row "3",MAXX(CURRENTGROUP(),[Column1]) // Get the MAX of the first Column and put it in a column named "3" ), "Row","1" // Add a row identifier ), "Row",[Row], // Select only our Row and 3 columns "3",[3] ) VAR __row14 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row4, // We first filter our table to a single row [Index], // Group by row "4",MAXX(CURRENTGROUP(),[Column1]) // Get the MAX of the first Column and put it in a column named "4" ), "Row","1" // Add a row identifier ), "Row",[Row], // Select only our Row and 4 columns "4",[4] ) // Second row VAR __row21 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row1, [Index], "1",MAXX(CURRENTGROUP(),[Column2]) // Column 1 of second row, get Column2 ), "Row","2" // This is the 2nd row of the transposed table ), "Row",[Row], // Select only our Row and 1 columns "1",[1] ) VAR __row22 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row2, [Index], "2",MAXX(CURRENTGROUP(),[Column2]) // Column 2 of second row ), "Row","2" // Row 2 ), "Row",[Row], // Select only our Row and 2 columns "2",[2] ) VAR __row23 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row3, [Index], "3",MAXX(CURRENTGROUP(),[Column2]) // Column 3 of second row ), "Row","2" // Row 2 ), "Row",[Row], // Select only our Row and 2 columns "3",[3] ) VAR __row24 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row4, [Index], "4",MAXX(CURRENTGROUP(),[Column2]) // Column 4 of second row ), "Row","2" // Row 2 ), "Row",[Row], // Select only our Row and 4 columns "4",[4] ) // Third row VAR __row31 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row1, [Index], "1",MAXX(CURRENTGROUP(),[Column3]) // Column3 ), "Row","3" // Row 3 ), "Row",[Row], "1",[1] ) VAR __row32 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row2, [Index], "2",MAXX(CURRENTGROUP(),[Column3]) // Second column ), "Row","3" ), "Row",[Row], "2",[2] ) VAR __row33 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row3, [Index], "3",MAXX(CURRENTGROUP(),[Column3]) // Third column ), "Row","3" ), "Row",[Row], "3",[3] ) VAR __row34 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row4, [Index], "4",MAXX(CURRENTGROUP(),[Column3]) // Fourth column ), "Row","3" ), "Row",[Row], "4",[4] ) // Fourth row VAR __row41 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row1, [Index], "1",MAXX(CURRENTGROUP(),[Column4]) // Column4 ), "Row","4" // Row 4 ), "Row",[Row], "1",[1] ) VAR __row42 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row2, [Index], "2",MAXX(CURRENTGROUP(),[Column4]) // Second column ), "Row","4" // Row 4 ), "Row",[Row], "2",[2] ) VAR __row43 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row3, [Index], "3",MAXX(CURRENTGROUP(),[Column4]) // Third column ), "Row","4" // Row 4 ), "Row",[Row], "3",[3] ) VAR __row44 = SELECTCOLUMNS( ADDCOLUMNS( GROUPBY( __row4, [Index], "4",MAXX(CURRENTGROUP(),[Column4]) // Fourth column ), "Row","4" // Row 4 ), "Row",[Row], "4",[4] ) // Join the rows together
// Row 1 VAR __transposedRow1a = NATURALINNERJOIN(__row11,__row12) VAR __transposedRow1b = NATURALINNERJOIN(__row13,__row14) VAR __transposedRow1 = NATURALINNERJOIN(__transposedRow1a,__transposedRow1b)
// Row 2 VAR __transposedRow2a = NATURALINNERJOIN(__row21,__row22) VAR __transposedRow2b = NATURALINNERJOIN(__row23,__row24) VAR __transposedRow2 = NATURALINNERJOIN(__transposedRow2a,__transposedRow2b)
// Row 3 VAR __transposedRow3a = NATURALINNERJOIN(__row31,__row32) VAR __transposedRow3b = NATURALINNERJOIN(__row33,__row34) VAR __transposedRow3 = NATURALINNERJOIN(__transposedRow3a,__transposedRow3b)
// Row 4 VAR __transposedRow4a = NATURALINNERJOIN(__row41,__row42) VAR __transposedRow4b = NATURALINNERJOIN(__row43,__row44) VAR __transposedRow4 = NATURALINNERJOIN(__transposedRow4a,__transposedRow4b)
// Union these to generate a transposed table VAR __transposedTable = UNION(__transposedRow1,__transposedRow2,__transposedRow3,__transposedRow4) RETURN __transposedTable
eyJrIjoiNzI3NjMyMjgtMGE0Zi00ZDQxLTgwZTYtNDZlMjVjYmQxNjI3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9