cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
some1else
Frequent Visitor

Transpose using DAX and virtual table

Hi guys.

 

If I were to transpose columns into rows like the image below, how could I achieve it using a virtual table in a measure? I don't want to create a copy of the existing table and store it in the model.

 

From my understanding this should be possible using UNION, SELECTCOLUMNS or maybe SUMMARIZE, but I'm struggling to get them right.

 

Thanks in advance.transpose.jpg

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @some1else 

The easiest way to get the results you want is to transpose them in the Query Editor.

Sample data:

v-angzheng-msft_0-1617875066082.jpeg

Step 1: Use Headers as First Row

Step 2: Transpose

v-angzheng-msft_1-1617875066085.jpeg

Result:

v-angzheng-msft_2-1617875066087.jpeg

 

 

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

 

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

Hi, @some1else 

The easiest way to get the results you want is to transpose them in the Query Editor.

Sample data:

v-angzheng-msft_0-1617875066082.jpeg

Step 1: Use Headers as First Row

Step 2: Transpose

v-angzheng-msft_1-1617875066085.jpeg

Result:

v-angzheng-msft_2-1617875066087.jpeg

 

 

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

 

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

The bigger question is why you have the first table in the model. The first table does not look like a structure that is well suited to PBI. Does the table really have just 2 rows?  Consider transposing the first table and loading it once in the transposed state. You can do this in power query

1. Use headers as first row

2. Transpose

3. Rename the first column "header"

3. Unpivot the 3 column table into 2 columns


this should give you 3 new columns (header, attribute, value). 

from there you can use a matrix with header on rows, attribute on column, and value in the values section. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
selimovd
Community Champion
Community Champion

Hey @some1else ,

 

a close approach is the following table:

UNION (
    SELECTCOLUMNS ( myTable, "ID", "A", "Value", [A] ),
    SELECTCOLUMNS ( myTable, "ID", "B", "Value", [B] ),
    SELECTCOLUMNS ( myTable, "ID", "C", "Value", [C] ),
    SELECTCOLUMNS ( myTable, "ID", "D", "Value", [D] )
)

 

Check if that would work, that's more or less a unpivot of the table.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Feeling very dumb right now...

 

Tried that but no sucess as it gives an error expecting one value only (tried with a measure, because I don't want to physically create columns or new tables).

Below image with the error and image with table OTs with the columns currently on it.

 

The idea is to have the WONUM and ZMC in row and the values in columns

 

error1.jpg

 

error2.jpg

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors