Transposing multiple tables

[Nanaia]

I have a worksheet that has 5 tables on it and all of them are transposed with the weekly breakdown going by column instead of by row. I'm trying to find a way to (easily) transpose all 5 tables so I can pull the data from them into a combined table more efficiently. I'd like to avoid having to select every cell needed to create a chart for comparing weeks. Is there a command that will do this or do I need to go through every cell of every table to accomplish this?
Thanks.

 

[alansidman]

This appears to be something you can do with Power Query/Get and Transform.

 

[Nanaia]

Looking online to see how to do Power Query/Get and Transforming I've tested this. It looks like it will work except for one snag... the files I'm receiving are set up with the weeks in the columns and the criteria being tallied in the rows. Excel keeps looking at the columns and totaling them and I need it to look at the rows. I have five tables contained on one worksheet. (I use the term "table" as a description, they aren't formatted as a table within Excel. It's simply data entered and ordered as if it were a table) Each table set up the same and am scratching my head on how to combine the data without having to manually reorder it. It would be easy if it was just one worksheet, but I have 34 worksheets set up similarly. Once I get one figured out, I will apply the same strategy to the rest. I am trying to get the data organized so I can create a chart based on the row headers instead of the column headers. This is the first time I've had to work with worksheets compiled by someone else so I guess I'm struggling a bit.

Here is an example for the first month:

2018 - AZ	11/26/18	12/03/18	12/10/18	12/17/18	12/24/18	Dec-18
External Weight	83,286.03	174,206.64	136,467.28	149,441.31	25,770.02	569,171.28
Internal Weight	12,860.56	28,575.24	6,449.36	39,892.54	950.26	88,727.96
Outside Jobs						-
Transferred Weight						-
Total Weight	96,146.59	202,781.88	142,916.64	189,333.85	26,720.28	657,899.24
Mfg. Weight	93,496.38	199,081.40	139,814.50	187,221.59	26,390.75	646,004.62
External Tons
Internal Tons
Outside Tonnage
Transferred Tons
Total Tons
Total Mfg Tons
Man Hours	1,765.50	1,499.10	1,743.07	1,591.50	696.92	7,296.09
Man Hours per Ton	36.73	14.79	24.39	16.81	52.16
Mfg Man Hrs per Ton	37.77	15.06	24.93	17.00	52.82

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
[TD="align: right"]41.64[/TD]
[TD="align: right"]87.10[/TD]
[TD="align: right"]68.23[/TD]
[TD="align: right"]74.72[/TD]
[TD="align: right"]12.89[/TD]
[TD="align: right"]284.59[/TD]

[TD="align: right"]6.43[/TD]
[TD="align: right"]14.29[/TD]
[TD="align: right"]3.22[/TD]
[TD="align: right"]19.95[/TD]
[TD="align: right"]0.48[/TD]
[TD="align: right"]44.36[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: right"]48.07[/TD]
[TD="align: right"]101.39[/TD]
[TD="align: right"]71.46[/TD]
[TD="align: right"]94.67[/TD]
[TD="align: right"]13.36[/TD]
[TD="align: right"]328.95[/TD]

[TD="align: right"]46.75[/TD]
[TD="align: right"]99.54[/TD]
[TD="align: right"]69.91[/TD]
[TD="align: right"]93.61[/TD]
[TD="align: right"]13.20[/TD]
[TD="align: right"]323.00[/TD]

[TD="align: right"]22.18[/TD]

[TD="align: right"]22.59[/TD]

</tbody>

 

[alansidman]

I have tranposed the data to look like below. If this is what you are looking for, then the Mcode follows.

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[TD]

P​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

2018 - AZ​

[/TD]
[TD]

External Weight​

[/TD]
[TD]

Internal Weight​

[/TD]
[TD]

Outside Jobs​

[/TD]
[TD]

Transferred Weight​

[/TD]
[TD]

Total Weight​

[/TD]
[TD]

Mfg. Weight​

[/TD]
[TD]

External Tons​

[/TD]
[TD]

Internal Tons​

[/TD]
[TD]

Outside Tonnage​

[/TD]
[TD]

Transferred Tons​

[/TD]
[TD]

Total Tons​

[/TD]
[TD]

Total Mfg Tons​

[/TD]
[TD]

Man Hours​

[/TD]
[TD]

Man Hours per Ton​

[/TD]
[TD]

Mfg Man Hrs per Ton​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

11/26/2018​

[/TD]
[TD]

83286.03​

[/TD]
[TD]

12860.56​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

96146.59​

[/TD]
[TD]

93496.38​

[/TD]
[TD]

41.64​

[/TD]
[TD]

6.43​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

48.07​

[/TD]
[TD]

46.75​

[/TD]
[TD]

1765.5​

[/TD]
[TD]

36.73​

[/TD]
[TD]

37.77​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

12/3/2018​

[/TD]
[TD]

174206.64​

[/TD]
[TD]

28575.24​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

202781.88​

[/TD]
[TD]

199081.4​

[/TD]
[TD]

87.1​

[/TD]
[TD]

14.29​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

101.39​

[/TD]
[TD]

99.54​

[/TD]
[TD]

1499.1​

[/TD]
[TD]

14.79​

[/TD]
[TD]

15.06​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

12/10/2018​

[/TD]
[TD]

136467.28​

[/TD]
[TD]

6449.36​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

142916.64​

[/TD]
[TD]

139814.5​

[/TD]
[TD]

68.23​

[/TD]
[TD]

3.22​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

71.46​

[/TD]
[TD]

69.91​

[/TD]
[TD]

1743.07​

[/TD]
[TD]

24.39​

[/TD]
[TD]

24.93​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

12/17/2018​

[/TD]
[TD]

149441.31​

[/TD]
[TD]

39892.54​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

189333.85​

[/TD]
[TD]

187221.59​

[/TD]
[TD]

74.72​

[/TD]
[TD]

19.95​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

94.67​

[/TD]
[TD]

93.61​

[/TD]
[TD]

1591.5​

[/TD]
[TD]

16.81​

[/TD]
[TD]

17​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

12/24/2018​

[/TD]
[TD]

25770.02​

[/TD]
[TD]

950.26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

26720.28​

[/TD]
[TD]

26390.75​

[/TD]
[TD]

12.89​

[/TD]
[TD]

0.48​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

13.36​

[/TD]
[TD]

13.2​

[/TD]
[TD]

696.92​

[/TD]
[TD]

52.16​

[/TD]
[TD]

52.82​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

12/18/2019​

[/TD]
[TD]

569171.28​

[/TD]
[TD]

88727.96​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

657899.24​

[/TD]
[TD]

646004.62​

[/TD]
[TD]

284.59​

[/TD]
[TD]

44.36​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

328.95​

[/TD]
[TD]

323​

[/TD]
[TD]

7296.09​

[/TD]
[TD]

22.18​

[/TD]
[TD]

22.59​

[/TD]
[/TR]
</tbody>[/TABLE]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"2018 - AZ", type text}, {"11/26/2018", type number}, {"12/3/2018", type number}, {"12/10/2018", type number}, {"12/17/2018", type number}, {"12/24/2018", type number}, {"12/18/2019", type any}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"2018 - AZ", type date}, {"External Weight", type number}, {"Internal Weight", type number}, {"Outside Jobs", type text}, {"Transferred Weight", type text}, {"Total Weight", type number}, {"Mfg. Weight", type number}, {"External Tons", type number}, {"Internal Tons", type number}, {"Outside Tonnage", Int64.Type}, {"Transferred Tons", Int64.Type}, {"Total Tons", type number}, {"Total Mfg Tons", type number}, {"Man Hours", type number}, {"Man Hours per Ton", type number}, {"Mfg Man Hrs per Ton", type number}})
in
    #"Changed Type2"

 

[Nanaia]

Power Query worked to transpose all of them. Thanks.