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: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Baskar
Resident Rockstar
Resident Rockstar

Transpose column instead of rows in Power Query

‎06-20-2018 10:46 AM

Thanks Advanced !!!

 

Input DataInput Data

Expected O/P.

Have to transpose the last two column into multiple columns. 

Output DataOutput Data

 

@ImkeF

Labels:
Message 1 of 8
5,003 Views
0
1 ACCEPTED SOLUTION
ImkeF
Super User
Super User
In response to Anonymous

‎06-25-2018 07:11 AM

Please paste this code into the advanced editor and follow the steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZBPC8IwDMW/Sijsto620rEeh04dosj8c5k7bDPoQDZou+9v610YiLvkvSSQX3hlSbLaWNQ9pG2LxpCQFBtXuOSUO91T7uW42roqGF1jQ3ni/MJP81N6yHfOsYgJJ4rLSPkT6TmD5aW4Zv5UQqrw3xw69nXzQrADPNCCfSIkLLiNjIlYyQDu42ena4ug0VjdtbYbehN5RDzDg66RbBpHTOF8TVvNAJma2E8QH1f1Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CONSOLE = _t, SITE_CALL_NAME = _t, MTRB_SHORT_NAME = _t, METER_SHORT_NAME = _t, COMMODITY_ID = _t, TEST_DATE = _t, MF_COUNT = _t, MODIFIED_BY = _t, LINEARITY_DEVIATION_PERC = _t, AVG_of_DENSITY = _t, COMMENTS = _t, FLOW_MAX_PERC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CONSOLE", type text}, {"SITE_CALL_NAME", type text}, {"MTRB_SHORT_NAME", type text}, {"METER_SHORT_NAME", type text}, {"COMMODITY_ID", type text}, {"TEST_DATE", type date}, {"MF_COUNT", Int64.Type}, {"MODIFIED_BY", type text}, {"LINEARITY_DEVIATION_PERC", type number}, {"AVG_of_DENSITY", type number}, {"COMMENTS", type text}, {"FLOW_MAX_PERC", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"AVG_of_DENSITY", "COMMENTS"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"SITE_CALL_NAME", "MTRB_SHORT_NAME", "METER_SHORT_NAME", "COMMODITY_ID", "TEST_DATE", "MF_COUNT", "LINEARITY_DEVIATION_PERC"}, {{"All", each _, type table}, {"CountRows", each Table.RowCount(_), type number}}),
    AddTranspose = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.DemoteHeaders(Table.SelectColumns([All], {"MODIFIED_BY", "FLOW_MAX_PERC"})))),
    DynamicExpand = Table.ExpandTableColumn(AddTranspose, "Custom", List.Transform({1..List.Max(AddTranspose[CountRows])}, each "Column"&Text.From(_))),
    Cleanup = Table.RemoveColumns(DynamicExpand,{"All", "CountRows"})
in
    Cleanup

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Message 8 of 8
5,029 Views
7 REPLIES 7
ImkeF
Super User
Super User

‎06-20-2018 11:47 AM

Why? Your data has the perfect shape for the data model.

 

1) Group on all columns that shall not be transposed and select "All". That will return partitions of you table

2) Remove all columns from those tables that shall not be transposed from the partitions

3) Demote headers and transpose the partitions

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 2 of 8
4,993 Views
0
Baskar
Resident Rockstar
Resident Rockstar
In response to ImkeF

‎06-20-2018 08:40 PM

Soory @ImkeF i can't able to understand, can u explain me once again .

Message 3 of 8
4,983 Views
0
ImkeF
Super User
Super User
In response to Baskar

‎06-20-2018 09:38 PM

Hi @Baskar,

please upload sample data, that would probably be easier for me than writing long texts.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 4 of 8
4,979 Views
0
Anonymous
Not applicable
In response to ImkeF

‎06-25-2018 05:20 AM

Hi . On behalf of Baskar here i'm providing sample data which is parked below.

Could you please help in achiving by taking "Before transpose data" --> "After transpose data"... If possiable please provide us .pbix file whcih will be very helpful.

 

Before Transpose:

CONSOLESITE_CALL_NAMEMTRB_SHORT_NAMEMETER_SHORT_NAMECOMMODITY_IDTEST_DATEMF_COUNTMODIFIED_BYLINEARITY_DEVIATION_PERCAVG_of_DENSITYCOMMENTSFLOW_MAX_PERC
Eastern AccessRG151-1M-11PDH20Feb20183PISANIK0.02915.9RATE CURVE18
Eastern AccessRG151-1M-11PDH20Feb20183PISANIK0.02915.9RATE CURVE-unable to get the 80%&95% due to rate restrictions.36
Eastern AccessRG151-1M-11PDH20Feb20183PISANIK0.02915.9RATE CURVE50
Eastern AccessRG151-1M-12PDH20Feb20183PISANIK0915.9RATE CURVE19
Eastern AccessRG151-1M-12PDH20Feb20183PISANIK0915.9RATE CURVE36
Eastern AccessRG151-1M-12PDH20Feb20183PISANIK0915.9RATE CURVE50

 

After Transpose:

 

CONSOLESITE_CALL_NAMEMTRB_SHORT_NAMEMETER_SHORT_NAMECOMMODITY_IDTEST_DATEAVG_of_DENSITYLINEARITY_DEVIATION_PERCMF_COUNTSourceColumn1Column2Column3Column4Column5Column6Column7
Eastern AccessRG151-1M-11PDH20Feb2018915.90.023FLOW_MAX_PERC183650    
Eastern AccessRG151-1M-11PDH20Feb2018915.90.023MODIFIED_BYPISANIKPISANIKPISANIK    
Eastern AccessRG151-1M-12PDH20Feb2018915.903FLOW_MAX_PERC193650    
Eastern AccessRG151-1M-12PDH20Feb2018915.903MODIFIED_BYPISANIKPISANIKPISANIK    

 

In case of any further information required please let us know. Thanks.

Message 7 of 8
4,953 Views
0
ImkeF
Super User
Super User
In response to Anonymous

‎06-25-2018 07:11 AM

Please paste this code into the advanced editor and follow the steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZBPC8IwDMW/Sijsto620rEeh04dosj8c5k7bDPoQDZou+9v610YiLvkvSSQX3hlSbLaWNQ9pG2LxpCQFBtXuOSUO91T7uW42roqGF1jQ3ni/MJP81N6yHfOsYgJJ4rLSPkT6TmD5aW4Zv5UQqrw3xw69nXzQrADPNCCfSIkLLiNjIlYyQDu42ena4ug0VjdtbYbehN5RDzDg66RbBpHTOF8TVvNAJma2E8QH1f1Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CONSOLE = _t, SITE_CALL_NAME = _t, MTRB_SHORT_NAME = _t, METER_SHORT_NAME = _t, COMMODITY_ID = _t, TEST_DATE = _t, MF_COUNT = _t, MODIFIED_BY = _t, LINEARITY_DEVIATION_PERC = _t, AVG_of_DENSITY = _t, COMMENTS = _t, FLOW_MAX_PERC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CONSOLE", type text}, {"SITE_CALL_NAME", type text}, {"MTRB_SHORT_NAME", type text}, {"METER_SHORT_NAME", type text}, {"COMMODITY_ID", type text}, {"TEST_DATE", type date}, {"MF_COUNT", Int64.Type}, {"MODIFIED_BY", type text}, {"LINEARITY_DEVIATION_PERC", type number}, {"AVG_of_DENSITY", type number}, {"COMMENTS", type text}, {"FLOW_MAX_PERC", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"AVG_of_DENSITY", "COMMENTS"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"SITE_CALL_NAME", "MTRB_SHORT_NAME", "METER_SHORT_NAME", "COMMODITY_ID", "TEST_DATE", "MF_COUNT", "LINEARITY_DEVIATION_PERC"}, {{"All", each _, type table}, {"CountRows", each Table.RowCount(_), type number}}),
    AddTranspose = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose(Table.DemoteHeaders(Table.SelectColumns([All], {"MODIFIED_BY", "FLOW_MAX_PERC"})))),
    DynamicExpand = Table.ExpandTableColumn(AddTranspose, "Custom", List.Transform({1..List.Max(AddTranspose[CountRows])}, each "Column"&Text.From(_))),
    Cleanup = Table.RemoveColumns(DynamicExpand,{"All", "CountRows"})
in
    Cleanup

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 8 of 8
5,030 Views
Anonymous
Not applicable
In response to ImkeF

‎06-25-2018 03:48 AM

As requested here I'm providing sample data which is parked below.

We are looking for using Before transpose data and need to get output like After tranpose data.

@ImkeF possiable please provide .pbix file would help us alot. 

 

Before Transpose:

 

CONSOLESITE_CALL_NAMEMTRB_SHORT_NAMEMETER_SHORT_NAMECOMMODITY_IDTEST_DATEMF_COUNTMODIFIED_BYLINEARITY_DEVIATION_PERCAVG_of_DENSITYCOMMENTSFLOW_MAX_PERC
AthabascaEC101-1M-11CDB20Jan20184LADOUCER0.03913.9 45
AthabascaEC101-1M-11CDB20Jan20184LADOUCER0.03913.9RATE CURVE63
AthabascaEC101-1M-11CDB20Jan20184LADOUCER0.03913.9RATE CURVE80
AthabascaEC101-1M-11CDB20Jan20184LADOUCER0.03913.9RATE CURVE95
AthabascaEC101-1M-12CDB20Jan20184LADOUCER0.01914.3RATE CURVE37
AthabascaEC101-1M-12CDB20Jan20184LADOUCER0.01914.3RATE CURVE59
AthabascaEC101-1M-12CDB20Jan20184LADOUCER0.01914.3RATE CURVE78
AthabascaEC101-1M-12CDB20Jan20184LADOUCER0.01914.3RATE CURVE94
AthabascaEC101-1M-13CDB20Jan20184LADOUCER0.05914.3RATE CURVE36
AthabascaEC101-1M-13CDB20Jan20184LADOUCER0.05914.3RATE CURVE59
AthabascaEC101-1M-13CDB20Jan20184LADOUCER0.05914.3RATE CURVE80
AthabascaEC101-1M-13CDB20Jan20184LADOUCER0.05914.3RATE CURVE94
AthabascaEC101-1M-14CDB21Jan20184BARRE0.05913.9RATE CURVE43
AthabascaEC101-1M-14CDB21Jan20184BARRE0.05913.9RATE CURVE62
AthabascaEC101-1M-14CDB21Jan20184BARRE0.05913.9RATE CURVE82
AthabascaEC101-1M-14CDB21Jan20184BARRE0.05913.9RATE CURVE97
AthabascaTT152-1M-11BHB29Jan20185BENNETM30.05913.8RATE CURVE20
AthabascaTT152-1M-11BHB29Jan20185BENNETM30.05913.8RATE CURVE41
AthabascaTT152-1M-11BHB29Jan20185BENNETM30.05913.8RATE CURVE61
AthabascaTT152-1M-11BHB29Jan20185BENNETM30.05913.8RATE CURVE83
AthabascaTT152-1M-11BHB29Jan20185BENNETM30.05913.8RATE CURVE95
AthabascaTT152-1M-12BHB30Jan20185BENNETM30.07913.8RATE CURVE21
AthabascaTT152-1M-12BHB30Jan20185BENNETM30.07913.8RATE CURVE40
AthabascaTT152-1M-12BHB30Jan20185BENNETM30.07913.8RATE CURVE59
AthabascaTT152-1M-12BHB30Jan20185BENNETM30.07913.8RATE CURVE81
AthabascaTT152-1M-12BHB30Jan20185BENNETM30.07913.8RATE CURVE96
AthabascaTT152-1M-13BHB30Jan20185BENNETM30.09913.7RATE CURVE20
AthabascaTT152-1M-13BHB30Jan20185BENNETM30.09913.7RATE CURVE40
AthabascaTT152-1M-13BHB30Jan20185BENNETM30.09913.7RATE CURVE60
AthabascaTT152-1M-13BHB30Jan20185BENNETM30.09913.7RATE CURVE81
AthabascaTT152-1M-13BHB30Jan20185BENNETM30.09913.7RATE CURVE97
AthabascaTT152-1M-14BHB30Jan20184BENNETM30.09913.7RATE CURVE21
AthabascaTT152-1M-14BHB30Jan20184BENNETM30.09913.7RATE CURVE41
AthabascaTT152-1M-14BHB30Jan20184BENNETM30.09913.7RATE CURVE61
AthabascaTT152-1M-14BHB30Jan20184BENNETM30.09913.7RATE CURVE94
AthabascaTT152-1M-15BHB30Jan20185BENNETM30.03913.3RATE CURVE21
AthabascaTT152-1M-15BHB30Jan20185BENNETM30.03913.3RATE CURVE41
AthabascaTT152-1M-15BHB30Jan20185BENNETM30.03913.3RATE CURVE59
AthabascaTT152-1M-15BHB30Jan20185BENNETM30.03913.3RATE CURVE80
AthabascaTT152-1M-15BHB30Jan20185BENNETM30.03913.3RATE CURVE96

 

After Appling transpose data sould look like below

 

CONSOLESITE_CALL_NAMEMTRB_SHORT_NAMEMETER_SHORT_NAMECOMMODITY_IDTEST_DATEAVG_of_DENSITYLINEARITY_DEVIATION_PERCMF_COUNTSourceColumn1Column2Column3Column4Column5Column6Column7
AthabascaEC101-1M-11CDB20Jan2018913.90.034FLOW_MAX_PERC45638095   
AthabascaEC101-1M-11CDB20Jan2018913.90.034MODIFIED_BYLADOUCERLADOUCERLADOUCERLADOUCER   
AthabascaEC101-1M-12CDB20Jan2018914.30.014FLOW_MAX_PERC37597894   
AthabascaEC101-1M-12CDB20Jan2018914.30.014MODIFIED_BYLADOUCERLADOUCERLADOUCERLADOUCER   
AthabascaEC101-1M-13CDB20Jan2018914.30.054FLOW_MAX_PERC36598094   
AthabascaEC101-1M-13CDB20Jan2018914.30.054MODIFIED_BYLADOUCERLADOUCERLADOUCERLADOUCER   
AthabascaEC101-1M-14CDB21Jan2018913.90.054FLOW_MAX_PERC43628297   
AthabascaEC101-1M-14CDB21Jan2018913.90.054MODIFIED_BYBARREBARREBARREBARRE   
AthabascaTT152-1M-11BHB29Jan2018913.80.055FLOW_MAX_PERC2041618395  
AthabascaTT152-1M-11BHB29Jan2018913.80.055MODIFIED_BYBENNETM3BENNETM3BENNETM3BENNETM3BENNETM3  
AthabascaTT152-1M-12BHB30Jan2018913.80.075FLOW_MAX_PERC2140598196  
AthabascaTT152-1M-12BHB30Jan2018913.80.075MODIFIED_BYBENNETM3BENNETM3BENNETM3BENNETM3BENNETM3  
AthabascaTT152-1M-13BHB30Jan2018913.70.095FLOW_MAX_PERC2040608197  
AthabascaTT152-1M-13BHB30Jan2018913.70.095MODIFIED_BYBENNETM3BENNETM3BENNETM3BENNETM3BENNETM3  
AthabascaTT152-1M-14BHB30Jan2018913.70.094FLOW_MAX_PERC21416194   
AthabascaTT152-1M-14BHB30Jan2018913.70.094MODIFIED_BYBENNETM3BENNETM3BENNETM3BENNETM3   
AthabascaTT152-1M-15BHB30Jan2018913.30.035FLOW_MAX_PERC2141598096  
AthabascaTT152-1M-15BHB30Jan2018913.30.035MODIFIED_BYBENNETM3BENNETM3BENNETM3BENNETM3BENNETM3  
AthabascaTT152-1M-16BHB30Jan2018913.10.045FLOW_MAX_PERC2141618095  
AthabascaTT152-1M-16BHB30Jan2018913.10.045MODIFIED_BYBENNETM3BENNETM3BENNETM3BENNETM3BENNETM3  

 

In case any further information required please do let me know.

Message 5 of 8
4,941 Views
0
ImkeF
Super User
Super User
In response to Anonymous

‎06-25-2018 12:46 PM

Hi @Anonymous and @Baskar,

just saw that you uploaded new data. 

The solution I gave will adjust automatically do more rows/new columns.

Just replace the reference in the first step "Source" to your data and you're done.

I will not upload any pbix, the code I gave contains the solution.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 6 of 8
4,934 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All