Repeating rows to columns

[swmakin]

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

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

 

[Matt Allington]

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]

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

<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]

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

 

[jrnewport1115]

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]

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]

	0	1	2	3	4
Production Minutes	60	862	60	862	60

<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]

This is simple in PQ.
Try this code below (the data in Table1 are from your #1 post)

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

 

[swmakin]

Thank you billszysz, you sir are a genius!

Code is so simple yet output is so eloquent.

 

[Useful]

Hello,
Also if you want to get the result that regardless from repeating position then try:

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