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

How to Transpose Columns in a table

Hi Friends

 

This is Nagarjuna. I Have a small query, Can u please help me on this?

 

I was created the below table in Power BI

 

Column A SumColumn B SumColumn C SumColumn D SumColumn E SumColumn F SumColumn G SumColumn H Sum
100200150180210220230250

 

I want to transpose the above table. i.e i need the table like the below one

 

Column A Sum100
Column B Sum200
Column C Sum150
Column D Sum180
Column E Sum210
Column F Sum220
Column G Sum230
Column H Sum250

 

 

And i need the graph like below one

 

screenshot_20171011_123731.png

 

How this possible in power BI

 

 

Thanks in Advance

Regards

Nagarjuna

 

2 REPLIES 2
v-huizhn-msft Super Contributor
Super Contributor

Re: How to Transpose Columns in a table

Hi @Nagarjunav,

I reproduce your scenario and get expected result, please follow the steps below.

1. Right click your sample table->Edit Query, select all columns-> click unpivot columns(lighted in yellow)->click "Apply" on home page. You will transpose columns in the table.

1.PNG2.PNG

Here is my Power Query statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMgKThqZg0gIsYggmjcCkMZgEysbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A Sum" = _t, #"Column B Sum" = _t, #"Column C Sum" = _t, #"Column D Sum" = _t, #"Column E Sum" = _t, #"Column F Sum" = _t, #"Column G Sum" = _t, #"Column H Sum" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A Sum", Int64.Type}, {"Column B Sum", Int64.Type}, {"Column C Sum", Int64.Type}, {"Column D Sum", Int64.Type}, {"Column E Sum", Int64.Type}, {"Column F Sum", Int64.Type}, {"Column G Sum", Int64.Type}, {"Column H Sum", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"


2. You can line chart, select "Attribute" as x-axis, value as value, you will get expected result.

3.png

Best Regards,
Angelia

Nagarjunav Frequent Visitor
Frequent Visitor

Re: How to Transpose Columns in a table

Thanks for your reply @v-huizhn-msft

 

What i posted was not my raw-data, those columns are the calculated columns. I Have 30 columns raw-data and 100 calculated columns.

 

I am changing my sample data for better understanding.

 

Here first 3 columns are Raw-data columns, 4th column onwards calculated ones

Transaction IDSupplier NameAmountAmount <250250<=Amount < 450 450<=Amount < 650 650<=Amount < 850 850<=Amount < 1050 1050<=Amount < 1250 1250<=Amount < 1450 Amount >1450
1A100100       
2B200200       
3C300 300      
4D400 400      
5E500  500     
6F600  600     
7G700   700    
8H800   800    
9B900    900   
10E1000    1000   
11H1100     1100  
12F1200     1200  
13C1300      1300 
14A1400      1400 
15E1500       1500

 

 

Now i need the table like below one

 

 

RangeAmount
Amount <250300
250<=Amount < 450 700
450<=Amount < 650 1100
650<=Amount < 850 1500
850<=Amount < 1050 1900
1050<=Amount < 1250 2300
1250<=Amount < 1450 2700
Amount >14501500

 

If the table comes like this, automatically we can draw the required graph

i.e. 

 

screenshot_20171012_120531.png

 

 

Thanks in advance

Regards

Nagarjuna