Transpose one column except first

[Anonymous]

I have a table as below. 

I would like to change this to:

Column1:    Column2    Column3    Column4   Column5

213              86               2

446              83              4                 2                6   

[Ashish_Mathur]

Hi,

Try this M code

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    Partition = Table.Group(#"Changed Type", {"Column1"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Column2", "Index"}, {"Column2", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Column2")
in
    #"Pivoted Column"

Hope this helps.

[kentyler]

It looks like you could go into power query and pivot the table on column one, which would give you a column each for the different values.

It looks like you don't want to pivot on column 2, you should be able to leave it alone when you do the pivot on column 1

[HotChilli · Accepted solution]

Here's my attempt.

I think you need to add a column (to index each value within the group) that can be used as the pivot column to make the  headings in the final table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7JDcAwCAR74e1HOGySWiz330bkS5rfjGBhexdTlyJvk1Gu2OKINgcOCTCXdtirzZ0HctJbKtjA/kGSA7AyoTylATlNEv8Sp/KWGj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"all", each _, type table [Column1=number, Column2=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "ind", 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "ind"}, {"Custom.Column1", "Custom.Column2", "Custom.ind"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.ind", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.ind", type text}}, "en-GB")[Custom.ind]), "Custom.ind", "Custom.Column2", List.Sum)
in
    #"Pivoted Column"

Tip: for quicker answers, please post the data (not a picture)

[Anonymous]

Thank you so much. This worked perfect