Forum Discussion
Transpose one column except first
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
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)
4 Replies
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.
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
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)
