cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 25 members 790 guests
Please welcome our newest community members: