Transpose Data to Create new table

[zifu886]

Hi Expert,

Im learning excel while I work on parsing large amount data. Need your help me to create a new table from old table as listed below. Your help is highly appreciated. Thank you once again in advance for your great help,

Sincerely
Zi

Source Table
Order_Aging	Company	Order Date	Order	Order Due Date	Amount
1 - 30	All-Good	12/11/2018	1234	12/11/2018	2400
31 - 60	All-Good	11/26/2018	1200	11/26/2018	7200
Current	UC-Tower	12/21/2018	#40	12/31/2018	1100
1 - 30	UC-Tower	12/7/2018	135		200
- 90	UC-Tower	12/7/2018	#38	12/17/2018	100
> 90	UC-Tower	12/21/2018	136		15000
current	Hello.com	12/17/2018	#7657	1/16/2019	90
current	Hello.com	12/31/2018	72	1/10/2019	100
New Table
Order_Aging	Company	Order Date	Order	Order Due Date	Current	1 - 30	31 - 60	61 - 90	> 90	Amount
1 - 30	All-Good	12/11/2018	1234	12/11/2018		2400				2400
31 - 60	All-Good	11/26/2018	1200	11/26/2018			2400			2400
current	UC-Tower	12/21/2018	#40	12/31/2018	1100					1100
1 - 30	UC-Tower	12/7/2018	135			200				200
61 - 90	UC-Tower	12/7/2018	#38	12/17/2018				100		100
> 90	UC-Tower	12/21/2018	136						15000	15000
current	Hello.com	12/17/2018	#7657	1/16/2019	90					90
current	Hello.com	12/31/2018	72	1/10/2019	100					100

<tbody>
</tbody>

 

[Special-K99]

I doubt I'll answer this as I'm busy at work but

in the New Table

why does 2400 appear in the 31-60 column for All-Good 11/26/2018 ?
The value is 7200 which doesn't appear ANYWHERE in the New Table

 

[sandy666]

with PowerQuery (Get&Transform)

source

result

Order_Aging

Company

Order Date

Order

Order Due Date

Amount

Order_Aging

Company

Order Date

Order

Order Due Date

current

1 - 30

31 - 60

-90

> 90

Amount

1 - 30

All-Good

11/12/2018​

1234​

11/12/2018​

2400​

1 - 30

All-Good

11/12/2018​

1234

11/12/2018​

2400​

2400​

31 - 60

All-Good

26/11/2018​

1200​

26/11/2018​

7200​

31 - 60

All-Good

26/11/2018​

1200

26/11/2018​

7200​

7200​

current

UC-Tower

21/12/2018​

#40

31/12/2018​

1100​

current

UC-Tower

21/12/2018​

#40

31/12/2018​

1100​

1100​

1 - 30

UC-Tower

07/12/2018​

135​

200​

1 - 30

UC-Tower

07/12/2018​

135

200​

200​

-90​

UC-Tower

07/12/2018​

#38

17/12/2018​

100​

-90

UC-Tower

07/12/2018​

#38

17/12/2018​

100​

100​

> 90

UC-Tower

21/12/2018​

136​

15000​

> 90

UC-Tower

21/12/2018​

136

15000​

15000​

current

Hello.com

17/12/2018​

#7657

16/01/2019​

90​

current

Hello.com

17/12/2018​

#7657

16/01/2019​

90​

90​

current

Hello.com

31/12/2018​

72​

10/01/2019​

100​

current

Hello.com

31/12/2018​

72

10/01/2019​

100​

100​

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Order_Aging", type text}, {"Company", type text}, {"Order Date", type date}, {"Order", type text}, {"Order Due Date", type date}, {"Amount", Int64.Type}}),
    Index = Table.AddIndexColumn(Type, "Index", 0, 1),
    Duplicta1 = Table.DuplicateColumn(Index, "Order_Aging", "Order_Aging - Copy"),
    Duplicate2 = Table.DuplicateColumn(Duplicta1, "Amount", "Amount - Copy"),
    Pivot = Table.Pivot(Duplicate2, List.Distinct(Duplicate2[#"Order_Aging - Copy"]), "Order_Aging - Copy", "Amount - Copy", List.Sum),
    Sort = Table.Sort(Pivot,{{"Index", Order.Ascending}}),
    RC = Table.RemoveColumns(Sort,{"Index"}),
    REorder = Table.ReorderColumns(RC,{"Order_Aging", "Company", "Order Date", "Order", "Order Due Date", "current", "1 - 30", "31 - 60", "-90", "> 90", "Amount"})
in
    REorder[/SIZE]

 

[zifu886]

Hi Special-K99,
good afternoon, Thanks much for your reply. That is a typo when I transferred the data. the All-Good 11/26/2018 for 31-60 should be "7200" and amount should be updated to 7200 as well. What I tried to achieve is to convert the source table to a new table by inserting the new Column(s) and each column's title = the unique value from Column A, then copy the amount.value to the corresponded new Column (eg, A2="1-30", then copy amount.value (row#2) ) to the new Column (1-30) being created), so on and so forth. Does this make sense? Thank you very much for your help,

Sincerely
Zi

 

[zifu886]

Hi Sandy666,

good morning, Thank you very much for your the M-codes. I used 2013 excel & honestly, I am not familiar with the power query, but tried to download pwoerquery onto excel, and did quick learning to run the power but when I run the M-code, I keep getting below errors, could you please provide me some guidances to run the run. I appreciate your time & help, thank you once again,

Sincerely
Zi

-------------------------------------------------------------------------
Expression.Error: We couldn't find an Excel table named 'Table1'.
Details:
Table1

-------------------------------------------------------------------------

 

[sandy666]

because your source range must be a table, Excel Table ! (see source in my post)
Use Ctrl+T then check in name manager the name of this table and change the name in the code if neccesary

 

[zifu886]

Hi Sandy666,

Great, I was able to run M-Code with Power Query and result is just perfect that I am looking for it, Pwer Query is powerful but I am totally just a beginner & need deeping more about it. Do you know how to use a macro to achieve the same results. Much Appreciate & thank you once again for your great help,

Sincerely
Zi

 

[alansidman]

Zi,
A really good starting point for PQ is Ken Puls book "M is for (Data) Monkey"

 

[zifu886]

Hi Alan

thanks much for your info. I found it from Amazon & ordered one. thanks again for your info,

Have a nice weekend,

Sincerely
Zi

 

[sandy666]

I am totally just a beginner & need deeping more about it.

• What is Power Query
• Getting Started with Get & Transform in Excel
• Microsoft Power Query for Excel Help

Do you know how to use a macro to achieve the same results

No

Much Appreciate & thank you

You are welcome

Have a nice day