Excel Transpose Help

[Mac1206]

Hello, I a have a spreadsheet of about 1600 rows in which I need to get into another format....

Part	ID	Amount
79123	5233122	44.18
79123	11606476	95.1
79123	22346192	149.81
79314	12129561	72.77
79314	15504217	79.24
79314	17257196	36.89
79314	20765123	37.11
79314	19830543	66.63
79314	22166231	34.58
79314	24132158	73.11

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

My result need to look like this and I don't have a clue to how I'm going to get through this? Thanks for your support....

Part	ID 1	ID 2	ID 3	ID 4	ID 5	ID 6	ID 7	Amt 1	Amt 2	Amt 3	Amt 4	Amt 5	Amt 6	Amt 7
79123	5233122	11606476	22346192					44.18	95.1	149.81
79314	12129561	15504217	17257196	20765123	19830543	22166231	24132158	72.77	79.24	36.89	37.11	66.63	34.58	73.11

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[sandy666]

with PowerQuery aka Get&Transform

Part

ID

Amount

Part

ID.1

ID.2

ID.3

ID.4

ID.5

ID.6

ID.7

Amount.1

Amount.2

Amount.3

Amount.4

Amount.5

Amount.6

Amount.7

79123​

5233122​

44.18​

79123

5233122

11606476

22346192

44.18​

95.1​

149.81​

79123​

11606476​

95.1​

79314

12129561

15504217

17257196

20765123

19830543

22166231

24132158

72.77​

79.24​

36.89​

37.11​

66.63​

34.58​

73.11​

79123​

22346192​

149.81​

79314​

12129561​

72.77​

79314​

15504217​

79.24​

79314​

17257196​

36.89​

79314​

20765123​

37.11​

79314​

19830543​

66.63​

79314​

22166231​

34.58​

79314​

24132158​

73.11​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Part"}, {{"Count", each _, type table}}),
    List1 = Table.AddColumn(#"Grouped Rows", "ID", each Table.Column([Count],"ID")),
    List2 = Table.AddColumn(List1, "Amount", each Table.Column([Count],"Amount")),
    Extract1 = Table.TransformColumns(List2, {"ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Extract2 = Table.TransformColumns(Extract1, {"Amount", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split1 = Table.SplitColumn(Extract2, "ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ID.1", "ID.2", "ID.3", "ID.4", "ID.5", "ID.6", "ID.7"}),
    Split2 = Table.SplitColumn(Split1, "Amount", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Amount.1", "Amount.2", "Amount.3", "Amount.4", "Amount.5", "Amount.6", "Amount.7"}),
    Type = Table.TransformColumnTypes(Split2,{{"ID.1", type text}, {"ID.2", type text}, {"ID.3", type text}, {"ID.4", type text}, {"ID.5", type text}, {"ID.6", type text}, {"ID.7", type text}, {"Amount.1", type number}, {"Amount.2", type number}, {"Amount.3", type number}, {"Amount.4", type number}, {"Amount.5", type number}, {"Amount.6", type number}, {"Amount.7", type number}, {"Part", type text}})
in
    Type[/SIZE]

and NO this is not a macro/vba

 

[Mac1206]

Thanks Sandy,

I never used Power Query before so where can I find the steps to implement this Code above...

 

[sandy666]

Here is example for this M-code

Excel Transpose

go to Data - Show Queries - dbl click on the table in Workbook Queries - then look for Advanced Editor and you'll see this M-code

I hope you are not on Mac ?

 

[Mac1206]

Thanks Sandy,

Thanks, I have the advance editor opened in my workbook for sheet 3, can I update it from here to the code you provided to make it work:

Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet3_Sheet,{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}})

 

[sandy666]

in my M-code are steps for your example from post#1
I don't know structure your actually table so I don't know you can or not but of course you can adapt steps or whole M-code to your needs

If headers and structure are the same as in code you can try copy/paste but if there will be one comma less or more you'll get error and you'll need to check why. In editor errors are usually highlighted.

 

[sandy666]

try with this:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Part"}, {{"Count", each _, type table}}),
    List1 = Table.AddColumn(#"Grouped Rows", "ID", each Table.Column([Count],"ID")),
    List2 = Table.AddColumn(List1, "Amount", each Table.Column([Count],"Amount")),
    Extract1 = Table.TransformColumns(List2, {"ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Extract2 = Table.TransformColumns(Extract1, {"Amount", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Reverse = Table.ReverseRows(Extract2),
    Split1 = Table.SplitColumn(Reverse, "ID", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv)),
    Split2 = Table.SplitColumn(Split1, "Amount", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv)),
    ReverseBack = Table.ReverseRows(Split2)

in
    ReverseBack[/SIZE]

Reverse moving longest string to the top position in the table and after all ReverseBack back to the original position

or post your actual file

 

[Mac1206]

Hi Sandy,

This is what I have thus far, how do I add the List1 = Table.AddColumn(#"Grouped Rows", "ID", each Table.Column([Count],"ID")), step....

let
Source = Excel.Workbook(File.Contents("C:\Users\Mac1206\Desktop\Data.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"Part"}, {{"Count", each _, type table}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Part", Int64.Type}, {"ID", Int64.Type}, {"Amount", type number}})
in
#"Changed Type"

 

[sandy666]

Could you post link to shared (GoogleDrive, OneDrive or any similar) excel file with example of source data which contain exactly structure as in original ?
I assume expected result is like in post#1

 

[Mac1206]

Here you go Sandy and thanks...

https://drive.google.com/file/d/10hpE7jBrU0Cynrt7uTkJgtYgrO4OexwX/view?usp=sharing