Repeating rows to columns

swmakin

New Member
Joined
Apr 29, 2016
Messages
5
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?


Column1.1Column1.2
Start Time2016/04/26 23:29
Production Minutes60mins
Target Weight260g
Target T19g
Target T218g
No of Weights4167
No of Good Weights4155
No of Under Weights3
No of Over Weights1
No of Check Weights0
No of Unstable Weights4
No of Over Capacity Weights0
No of Weights Between T1/T21
No of Under Weight T1 Rejects0
No of Under Weight T2 Rejects0
No of Under Weights Below T23
Total Weight1120523g
Total Good Weight1119522g
Total Under Weight593g
Total Over Weight408g
Total Weight Between T1/T2248g
Total Under Weight T1 Rejects0g
Total Under Weight T2 Rejects0g
Total Under Weight Below T2593g
Average Weight269g
Standard Deviation4.28g
Giveaway3.6%
Percentage Weight Between T1/T20.02%
Percentage Count Between T1/T20.02%
Total Good Weight T111867g
No of Good Weights T146
Start Time26/04/16 07:13
Production Minutes862mins
Target Weight280.0g
Target T19.0g
Target T218.0g
No of Weights24027
No of Good Weights20882
No of Under Weights16
No of Over Weights0
No of Check Weights0
No of Unstable Weights3084
No of Over Capacity Weights0
No of Weights Between T1/T20
No of Under Weight T1 Rejects0
No of Under Weight T2 Rejects0
No of Under Weights Below T216
Total Weight7215882.6g
Total Good Weight7212657.5g

<colgroup><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,192
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.
 

swmakin

New Member
Joined
Apr 29, 2016
Messages
5
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


Start TimeProduction MinutesTarget WeightTarget T1Target T2No of WeightsNo of Good WeightsNo of Under WeightsNo of Over WeightsNo of Check WeightsNo of Unstable WeightsNo of Over Capacity WeightsNo of Weights Between T1/T2No of Under Weight T1 RejectsNo of Under Weight T2 RejectsNo of Under Weights Below T2Total WeightTotal Good WeightTotal Under WeightTotal Over WeightTotal Weight Between T1/T2Total Under Weight T1 RejectsTotal Under Weight T2 RejectsTotal Under Weight Below T2Average WeightStandard DeviationGiveawayPercentage Weight Between T1/T2Percentage Count Between T1/T2Total Good Weight T1No of Good Weights T1
2016/04/26 23:2960mins260g9g18g416741553104010031120523g1119522g593g408g248g0g0g593g269g4.28g3.6%0.02%0.02%11867g46
26/04/16 07:13862mins280.0g9.0g18.0g2402720882160030840000167215882.6g7212657.5g3225.1g0.0g0.0g0.0g0.0g3225.1g345.4g80.55g23.4%0.00%0.00%1650.3g6
2016/04/26 20:5360mins190g9g17g581457611126090100111175888g1164817g1611g9460g175g0g0g1611g202g4.63g6.4%0.01%0.01%1880g10
26/04/16 07:13862mins280.0g9.0g18.0g2402720882160030840000167215882.6g7212657.5g3225.1g0.0g0.0g0.0g0.0g3225.1g345.4g80.55g23.4%0.00%0.00%1650.3g6
2016/04/26 23:2960mins260g9g18g434743301309000011173903g1172504g219g1181g0g0g0g219g271g4.29g4.2%0.00%0.00%7238g28

<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>
</tbody>
 

jrnewport1115

New Member
Joined
Jun 17, 2014
Messages
13
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:

jrnewport1115

New Member
Joined
Jun 17, 2014
Messages
13
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.
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,192
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
 

jrnewport1115

New Member
Joined
Jun 17, 2014
Messages
13
01234
Production Minutes608626086260


<colgroup><col><col span="5"></colgroup><tbody>
</tbody>


The above is an example of the output of the second method I listed, using the production minutes attribute.
 

billszysz

Active Member
Joined
Feb 26, 2014
Messages
344
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:

swmakin

New Member
Joined
Apr 29, 2016
Messages
5
Thank you billszysz, you sir are a genius!

Code is so simple yet output is so eloquent.
 

Useful

Active Member
Joined
Mar 16, 2011
Messages
494
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
 

Forum statistics

Threads
1,086,120
Messages
5,387,964
Members
402,091
Latest member
thomastsiakis

Some videos you may like

This Week's Hot Topics

Top