Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
ak77
Post Patron
Post Patron

Transpose Columns to Rows Display

‎06-16-2023 04:31 PM

Hi All,

 

I have a table as in the 1st Pic and need a 2 column Transpose Table as mentioned in the 2nd pic. Please let me know if power bi has this option of transposing 

 

ak77_0-1686958189068.png

 

Labels:
Message 1 of 13
1,656 Views
0
2 ACCEPTED SOLUTIONS
VahidDM
Super User
Super User

‎06-16-2023 04:49 PM

Hi @ak77 

 

You can do that easily in Power query:

VahidDM_0-1686959347304.png

 

 

Please download the attached file:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

View solution in original post

Transpose Columns to Rows Display.pbix
Message 2 of 13
1,648 Views
Ahmedx
Super User
Super User

‎06-17-2023 09:37 AM

it is my variant? pls try it

 

 

 = Table.Combine( 
         List.Transform( Table.ToRows( #"Changed Type"),
                        (x)=> Table.FromRows( List.Split(x,2))))
----------------- or--------------
= Table.Combine( 
         List.Transform( Table.ToRows( #"Changed Type"),
                        (x)=> Table.FromRows( List.Split(x,2),{"Col1", "Col2"})))

 

 

Sample PBIX file attached

https://1drv.ms/u/s!AiUZ0Ws7G26RiHU5B_NG7o0HK6as?e=Xonqvs

 

View solution in original post

Message 4 of 13
1,581 Views
12 REPLIES 12
ak77
Post Patron
Post Patron

‎07-19-2023 12:02 PM

hi @VahidDM ,

 

The above solution worked perfectly as solution. Thanks for help and DXP.

Need one more help please if possible as the table is extended with new columns as below and need help in Transpose multiple columns

Can u please help 

I have the table as below : 

Table:

Col1Col2Col3Col4Col5Col6Col7Col8
a1a2a3a4a5a6a7a8
b1b2b3b4b5b6b7b8
c1c2c3c4c5c6c7c8

Result like this:

Col1Col2Col5Col6
a1a2a5a6
a3a4a5a6
a7a8a5a6
b1b2b5b6
b3b4b5b6
b7b8b5b6
c1c2c5c6
c3c4c5c6
c7c8c5c6

 

Message 13 of 13
1,177 Views
0
ak77
Post Patron
Post Patron

‎07-19-2023 12:02 PM

hi @VahidDM ,

 

The above solution worked perfectly as solution. Thanks for help and DXP.

Need one more help please if possible as the table is extended with new columns as below and need help in Transpose multiple columns

Can u please help 

I have the table as below : 

Table:

Col1Col2Col3Col4Col5Col6Col7Col8
a1a2a3a4a5a6a7a8
b1b2b3b4b5b6b7b8
c1c2c3c4c5c6c7c8

Result like this:

Col1Col2Col5Col6
a1a2a5a6
a3a4a5a6
a7a8a5a6
b1b2b5b6
b3b4b5b6
b7b8b5b6
c1c2c5c6
c3c4c5c6
c7c8c5c6

 

Message 11 of 13
1,177 Views
0
Ashish_Mathur
Super User
Super User
In response to ak77

‎07-19-2023 04:38 PM

Hi,

Share the actual column titles (rather than Col1-Col8)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 12 of 13
1,165 Views
0
ak77
Post Patron
Post Patron

‎06-19-2023 07:34 AM

Hi all, Thanks for help.. I was able to get the steps to achieve based on above

Message 10 of 13
1,401 Views
ak77
Post Patron
Post Patron

‎06-18-2023 01:05 PM

Hi @VahidDM ,

 

Please need ur help if the latest request is  possible with ur soluttion as i proceeded with ur answer

Message 9 of 13
1,442 Views
0
ak77
Post Patron
Post Patron

‎06-17-2023 04:49 PM

Hi all,

Thanks a lot for reply. Sorry i should have mentioned this before.. There are extra columns in the Table and that should get replicated . In the below example Col0 and Col5 Values are replicated . Can this be done? please suggest

ak77_0-1687045717315.png

 

 

 

Message 5 of 13
1,541 Views
0
Ahmedx
Super User
Super User
In response to ak77

‎06-17-2023 09:10 PM

to understand that it is easy to do, watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RiHY8BkGK6gF88Urd?e=oGtUsh

Sample PBIX file attached

https://1drv.ms/u/s!AiUZ0Ws7G26RiHfa4iY9me5PwT9B?e=qNfnUT

(r) => Table.Combine( 
         List.Transform( Table.ToRows(
                        Table.RemoveColumns(r, {Table.ColumnNames(r){0},Table.ColumnNames(r){5}})),(x)=> Table.FromRows( List.Split(x,2),{"Col1", "Col2"})))
Message 7 of 13
1,511 Views
0
ak77
Post Patron
Post Patron
In response to Ahmedx

‎07-27-2023 11:27 PM

Hi @Ahmedx ,

 

Thanks fr ur video. Can u please help the same logic for the below data set

2 new columns to be added in the result

NewCol1NewCol2

Each row should be copied to 2 more rows 

NewCol1 values will have class_scheme_node_name,class_scheme_node_name_benchmark ,ExcessReturn Header values

NewCol2 values will have log_portfolio_base, log_benchmark_base and ExcessReturn  values

 

 

data set

portfolio_codeclass_scheme_codeclass_scheme_node_nameclass_scheme_node_name_benchmarkclass_scheme_node_codedate_fromdate_tocontribution_basesequencereturn_portfolio_baselog_portfolio_baselog_benchmark_baselog_contribution_baseExcess_ReturnExcessReturnsHeaderclient_nameclient_typePortfolio_Node_Name_OverrideBenchmark_Node_Name_Override
Pool1class_scheme_code_11ACTIVE ASSET AND FX ALLOCATIONACTIVE ASSET AND FX ALLOCATION BenchmarkACTIVE_ASSET_FX_ALLOC06/30/202306/30/2023-0.00032.000.003.00417.963680.1652070-3.23E-060.082603Excess Returnsclient_name1EndowmentNULLNULL 
Pool1class_scheme_code_14CASH AND EQUIVALENTSCASH AND EQUIVALENTS BenchmarkCASHEQ06/30/202306/30/20230.000529.000.000.000.0000.0131120.0001310.0079165.29E-06-0.00389Excess Returnsclient_name4EndowmentNULLNULL 

Result:

NewCol1NewCol2portfolio_codeclass_scheme_codeclass_scheme_node_nameclass_scheme_node_name_benchmarkclass_scheme_node_codedate_fromdate_tocontribution_basesequencereturn_portfolio_baselog_portfolio_baselog_benchmark_baselog_contribution_baseExcess_ReturnExcessReturnsHeaderclient_nameclient_typePortfolio_Node_Name_OverrideBenchmark_Node_Name_Override
ACTIVE ASSET AND FX ALLOCATION3.23E-06Pool1class_scheme_code_11ACTIVE ASSET AND FX ALLOCATIONACTIVE ASSET AND FX ALLOCATION BenchmarkACTIVE_ASSET_FX_ALLOC06/30/202306/30/2023-0.00032.000.003.00417.963680.16520726.46E-060.082603Excess Returnsclient_name1EndowmentNULLNULL 
ACTIVE ASSET AND FX ALLOCATION Benchmark2Pool1class_scheme_code_11ACTIVE ASSET AND FX ALLOCATIONACTIVE ASSET AND FX ALLOCATION BenchmarkACTIVE_ASSET_FX_ALLOC06/30/202306/30/2023-0.00032.000.003.00417.963680.1652072-3.23E-060.082603Excess Returnsclient_name1EndowmentNULLNULL 
Excess Returns0.082603Pool1class_scheme_code_11ACTIVE ASSET AND FX ALLOCATIONACTIVE ASSET AND FX ALLOCATION BenchmarkACTIVE_ASSET_FX_ALLOC06/30/202306/30/2023-0.00032.000.003.00417.963680.1652072-3.23E-060.082603Excess Returnsclient_name1EndowmentNULLNULL 
CASH AND EQUIVALENTS0.000131Pool2class_scheme_code_14CASH AND EQUIVALENTSCASH AND EQUIVALENTS BenchmarkCASHEQ06/30/202306/30/20230.000529.000.000.000.0000.0131120.0001310.0079165.29E-06-0.00389Excess Returnsclient_name4EndowmentNULLNULL 
CASH AND EQUIVALENTS Benchmark0.007916Pool2class_scheme_code_14CASH AND EQUIVALENTSCASH AND EQUIVALENTS BenchmarkCASHEQ06/30/202306/30/20230.000529.000.000.000.0000.0131120.0001310.0079165.29E-06-0.00389Excess Returnsclient_name4EndowmentNULLNULL 
Excess Returns-0.00389Pool2class_scheme_code_14CASH AND EQUIVALENTSCASH AND EQUIVALENTS BenchmarkCASHEQ06/30/202306/30/20230.000529.000.000.000.0000.0131120.0001310.0079165.29E-06-0.00389Excess Returnsclient_name4EndowmentNULLNULL 

 

Message 8 of 13
1,105 Views
0
Ashish_Mathur
Super User
Super User
In response to ak77

‎06-17-2023 05:24 PM

Hi,

In the second step of my solution, select Col0, Col5 and the Index column and then "Unpivot Other columns". 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 6 of 13
1,535 Views
0
Ahmedx
Super User
Super User

‎06-17-2023 09:37 AM

it is my variant? pls try it

 

 

 = Table.Combine( 
         List.Transform( Table.ToRows( #"Changed Type"),
                        (x)=> Table.FromRows( List.Split(x,2))))
----------------- or--------------
= Table.Combine( 
         List.Transform( Table.ToRows( #"Changed Type"),
                        (x)=> Table.FromRows( List.Split(x,2),{"Col1", "Col2"})))

 

 

Sample PBIX file attached

https://1drv.ms/u/s!AiUZ0Ws7G26RiHU5B_NG7o0HK6as?e=Xonqvs

 

Message 4 of 13
1,582 Views
Ashish_Mathur
Super User
Super User

‎06-16-2023 07:15 PM

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.ExpandTableColumn(Table.Group(#"Removed Columns", {"Index"}, {{"Count", each Table.AddIndexColumn(_,"Column",1,1), type table }}), "Count", {"Value", "Column"}, {"Value", "Column"}),
    #"Calculated Modulo" = Table.TransformColumns(#"Grouped Rows", {{"Column", each Number.Mod(_, 2), type number}}),
    #"Grouped Rows1" = Table.ExpandTableColumn(Table.Group(#"Calculated Modulo", {"Column"}, {{"Count", each Table.AddIndexColumn(_,"Row",1,1), type table }}), "Count", {"Index", "Value", "Row"}, {"Index", "Value", "Row"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows1",{"Index"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Row", Order.Ascending}, {"Column", Order.Descending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Column", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Column", type text}}, "en-IN")[Column]), "Column", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Row"})
in
    #"Removed Columns2"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 3 of 13
1,602 Views
VahidDM
Super User
Super User

‎06-16-2023 04:49 PM

Hi @ak77 

 

You can do that easily in Power query:

VahidDM_0-1686959347304.png

 

 

Please download the attached file:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

Badges.jpg

LinkedIn | Twitter | Blog | YouTube 

Transpose Columns to Rows Display.pbix
Message 2 of 13
1,649 Views

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All