Repeating rows to columns

S

swmakin

New Member
Joined
Apr 29, 2016
Messages
5
[TABLE="width: 375"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]I have imported a txt file in to Query editor, separated in to two columns and cleansed the data to give me a data set with 31 unique row labels in column A and the information in column B. (see below)

Is there a way I can transform this data within query editor to show a table of 31 rows with the column data showing in columns B onwards?


[TABLE="width: 375"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column1.1[/TD]
[TD]Column1.2[/TD]
[/TR]
[TR]
[TD]Start Time[/TD]
[TD]2016/04/26 23:29[/TD]
[/TR]
[TR]
[TD]Production Minutes[/TD]
[TD]60mins[/TD]
[/TR]
[TR]
[TD]Target Weight[/TD]
[TD]260g[/TD]
[/TR]
[TR]
[TD]Target T1[/TD]
[TD]9g[/TD]
[/TR]
[TR]
[TD]Target T2[/TD]
[TD]18g[/TD]
[/TR]
[TR]
[TD]No of Weights[/TD]
[TD]4167[/TD]
[/TR]
[TR]
[TD]No of Good Weights[/TD]
[TD]4155[/TD]
[/TR]
[TR]
[TD]No of Under Weights[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]No of Over Weights[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]No of Check Weights[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Unstable Weights[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]No of Over Capacity Weights[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Weights Between T1/T2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]No of Under Weight T1 Rejects[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Under Weight T2 Rejects[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Under Weights Below T2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Total Weight[/TD]
[TD]1120523g[/TD]
[/TR]
[TR]
[TD]Total Good Weight[/TD]
[TD]1119522g[/TD]
[/TR]
[TR]
[TD]Total Under Weight[/TD]
[TD]593g[/TD]
[/TR]
[TR]
[TD]Total Over Weight[/TD]
[TD]408g[/TD]
[/TR]
[TR]
[TD]Total Weight Between T1/T2[/TD]
[TD]248g[/TD]
[/TR]
[TR]
[TD]Total Under Weight T1 Rejects[/TD]
[TD]0g[/TD]
[/TR]
[TR]
[TD]Total Under Weight T2 Rejects[/TD]
[TD]0g[/TD]
[/TR]
[TR]
[TD]Total Under Weight Below T2[/TD]
[TD]593g[/TD]
[/TR]
[TR]
[TD]Average Weight[/TD]
[TD]269g[/TD]
[/TR]
[TR]
[TD]Standard Deviation[/TD]
[TD]4.28g[/TD]
[/TR]
[TR]
[TD]Giveaway[/TD]
[TD]3.6%[/TD]
[/TR]
[TR]
[TD]Percentage Weight Between T1/T2[/TD]
[TD]0.02%[/TD]
[/TR]
[TR]
[TD]Percentage Count Between T1/T2[/TD]
[TD]0.02%[/TD]
[/TR]
[TR]
[TD]Total Good Weight T1[/TD]
[TD]11867g[/TD]
[/TR]
[TR]
[TD]No of Good Weights T1[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]Start Time[/TD]
[TD]26/04/16 07:13[/TD]
[/TR]
[TR]
[TD]Production Minutes[/TD]
[TD]862mins[/TD]
[/TR]
[TR]
[TD]Target Weight[/TD]
[TD]280.0g[/TD]
[/TR]
[TR]
[TD]Target T1[/TD]
[TD]9.0g[/TD]
[/TR]
[TR]
[TD]Target T2[/TD]
[TD]18.0g[/TD]
[/TR]
[TR]
[TD]No of Weights[/TD]
[TD]24027[/TD]
[/TR]
[TR]
[TD]No of Good Weights[/TD]
[TD]20882[/TD]
[/TR]
[TR]
[TD]No of Under Weights[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]No of Over Weights[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Check Weights[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Unstable Weights[/TD]
[TD]3084[/TD]
[/TR]
[TR]
[TD]No of Over Capacity Weights[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Weights Between T1/T2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Under Weight T1 Rejects[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Under Weight T2 Rejects[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]No of Under Weights Below T2[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Total Weight[/TD]
[TD]7215882.6g[/TD]
[/TR]
[TR]
[TD]Total Good Weight[/TD]
[TD]7212657.5g[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
So you want column A Blank? Just add a blank column and then change the ordering of the columns to add the new Blank column first.
 
Upvote 0
Apologies Matt, I clearly didn't make my query clear enough.

No, I want to turn a set the set of repeating data in to a table. In column A I have the repeating headings and in column B the data. Ideally I would like to have the headings (from column A) in the first row of a 31 column table with all the data showing in the subsequent rows. Similar to as shown below:

Thanks


[TABLE="width: 4300"]
<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col span="3"><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Start Time[/TD]
[TD]Production Minutes[/TD]
[TD]Target Weight[/TD]
[TD]Target T1[/TD]
[TD]Target T2[/TD]
[TD]No of Weights[/TD]
[TD]No of Good Weights[/TD]
[TD]No of Under Weights[/TD]
[TD]No of Over Weights[/TD]
[TD]No of Check Weights[/TD]
[TD]No of Unstable Weights[/TD]
[TD]No of Over Capacity Weights[/TD]
[TD]No of Weights Between T1/T2[/TD]
[TD]No of Under Weight T1 Rejects[/TD]
[TD]No of Under Weight T2 Rejects[/TD]
[TD]No of Under Weights Below T2[/TD]
[TD]Total Weight[/TD]
[TD]Total Good Weight[/TD]
[TD]Total Under Weight[/TD]
[TD]Total Over Weight[/TD]
[TD]Total Weight Between T1/T2[/TD]
[TD]Total Under Weight T1 Rejects[/TD]
[TD]Total Under Weight T2 Rejects[/TD]
[TD]Total Under Weight Below T2[/TD]
[TD]Average Weight[/TD]
[TD]Standard Deviation[/TD]
[TD]Giveaway[/TD]
[TD]Percentage Weight Between T1/T2[/TD]
[TD]Percentage Count Between T1/T2[/TD]
[TD]Total Good Weight T1[/TD]
[TD]No of Good Weights T1[/TD]
[/TR]
[TR]
[TD]2016/04/26 23:29[/TD]
[TD]60mins[/TD]
[TD]260g[/TD]
[TD]9g[/TD]
[TD]18g[/TD]
[TD]4167[/TD]
[TD]4155[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]1120523g[/TD]
[TD]1119522g[/TD]
[TD]593g[/TD]
[TD]408g[/TD]
[TD]248g[/TD]
[TD]0g[/TD]
[TD]0g[/TD]
[TD]593g[/TD]
[TD]269g[/TD]
[TD]4.28g[/TD]
[TD]3.6%[/TD]
[TD]0.02%[/TD]
[TD]0.02%[/TD]
[TD]11867g[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]26/04/16 07:13[/TD]
[TD]862mins[/TD]
[TD]280.0g[/TD]
[TD]9.0g[/TD]
[TD]18.0g[/TD]
[TD]24027[/TD]
[TD]20882[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3084[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]16[/TD]
[TD]7215882.6g[/TD]
[TD]7212657.5g[/TD]
[TD]3225.1g[/TD]
[TD]0.0g[/TD]
[TD]0.0g[/TD]
[TD]0.0g[/TD]
[TD]0.0g[/TD]
[TD]3225.1g[/TD]
[TD]345.4g[/TD]
[TD]80.55g[/TD]
[TD]23.4%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]1650.3g[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2016/04/26 20:53[/TD]
[TD]60mins[/TD]
[TD]190g[/TD]
[TD]9g[/TD]
[TD]17g[/TD]
[TD]5814[/TD]
[TD]5761[/TD]
[TD]11[/TD]
[TD]26[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]11[/TD]
[TD]1175888g[/TD]
[TD]1164817g[/TD]
[TD]1611g[/TD]
[TD]9460g[/TD]
[TD]175g[/TD]
[TD]0g[/TD]
[TD]0g[/TD]
[TD]1611g[/TD]
[TD]202g[/TD]
[TD]4.63g[/TD]
[TD]6.4%[/TD]
[TD]0.01%[/TD]
[TD]0.01%[/TD]
[TD]1880g[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]26/04/16 07:13[/TD]
[TD]862mins[/TD]
[TD]280.0g[/TD]
[TD]9.0g[/TD]
[TD]18.0g[/TD]
[TD]24027[/TD]
[TD]20882[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3084[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]16[/TD]
[TD]7215882.6g[/TD]
[TD]7212657.5g[/TD]
[TD]3225.1g[/TD]
[TD]0.0g[/TD]
[TD]0.0g[/TD]
[TD]0.0g[/TD]
[TD]0.0g[/TD]
[TD]3225.1g[/TD]
[TD]345.4g[/TD]
[TD]80.55g[/TD]
[TD]23.4%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]1650.3g[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2016/04/26 23:29[/TD]
[TD]60mins[/TD]
[TD]260g[/TD]
[TD]9g[/TD]
[TD]18g[/TD]
[TD]4347[/TD]
[TD]4330[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1173903g[/TD]
[TD]1172504g[/TD]
[TD]219g[/TD]
[TD]1181g[/TD]
[TD]0g[/TD]
[TD]0g[/TD]
[TD]0g[/TD]
[TD]219g[/TD]
[TD]271g[/TD]
[TD]4.29g[/TD]
[TD]4.2%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]7238g[/TD]
[TD]28[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Swmakin,

I think if you import your source table into Power Query, select each column in your table (with CTRL+click), and go to the "Transform" tab, and select "Unpivot Columns" in the "Any Column" group (the second group next to the "Table" group), you should get the results I think you're looking for? Hopefully I understand correctly
 
Last edited:
Upvote 0
If you're looking to simply move your data points horizontally rather than vertically,you can follow the same steps as above, but first add an index column, and include all your columns in the pivot except the index column, you should be able to load that into the data model, create a pivot table, and add the index values to the column criteria, the attributes to the row criteria, and your values to the value criteria.
 
Upvote 0
As I now understand the problem, the 31 unique values in column A are repeating and you want to keep the detail. So pivoting is not going to work, nor is transposing the rows with columns. If they didn't repeat, then transposing would work.

I guess it is possible in Power Query, but I guess it is not straight forward. I am thinking the following process (completely untested - just an idea of an approach)
Add an Index column
Build a function that extracts the first set of records (Index 1 - 31) into a single row of data with the column headings transposed
Count the number of sets of data
create a list of values (1, 2, 3...) that goes up to the number of sets of data.
Pass the above list to the function so that it can iterate through each set of data, one set at a time
append each row from the function into a single table.

This could also be done with VBA
 
Upvote 0
[TABLE="width: 616"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Production Minutes[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]862[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]862[/TD]
[TD="align: right"]60

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


The above is an example of the output of the second method I listed, using the production minutes attribute.
 
Upvote 0
This is simple in PQ.
Try this code below (the data in Table1 are from your #1 post)
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChType = Table.TransformColumnTypes(Source,{{"Column1.1", type text}, {"Column1.2", type any}}),
    AddIdx = Table.AddIndexColumn(ChType, "Indeks", 0, 1),
    IdxColTrunc31 = Table.TransformColumns(AddIdx, {{"Indeks", each Number.IntegerDivide(_, 31), Int64.Type}}),
    PivotCol = Table.Pivot(IdxColTrunc31, List.Distinct(IdxColTrunc31[Column1.1]), "Column1.1", "Column1.2"),
    RemoveIdxCol = Table.RemoveColumns(PivotCol,{"Indeks"})
in
    RemoveIdxCol

Regards

PS Everything is directly from UI
 
Last edited:
Upvote 0
Hello,
Also if you want to get the result that regardless from repeating position then try:
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Groupping = Table.FromColumns(Table.Group(Source, {"Column1.1"},
{{"Qrup", each List.InsertRange(_[Column1.2],0,List.Distinct([Column1.1]))}})[Qrup]),
    ProHed = Table.PromoteHeaders(Groupping)
in
    ProHed
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
VBA macro to input formlas in certain locations besed on identifiers
Replies
1
Views
519
thelad
T
C
  • Question Question
Copy Data From Multiple Columns in One Sheet to Another & Add Pre-Defined Value
2
Replies
13
Views
1K
skywriter
skywriter
B
  • Question Question
lookup whats several columns to the right
Replies
3
Views
382
BarryL
B
A
  • Question Question
Need to find only rows with Y in any of 5 columns ??
Replies
5
Views
530
Gingertrees
Gingertrees
L
  • Question Question
Commission Calculator using Points System
Replies
0
Views
2K
Lustful Salmon
L

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top