Rotate mi data using power query

[rubenamn]

Hi I have this data set

Each Machine Number has two tests per peel, my goal is to rotate each peel test from columns into rows in the right order.

for example, the machine M2116 has a Peel1 result in column form 105/129, I wanted to transform this group into rows 105-129, the same for Peel2,3,4, etc for each machine.

How Can I achieve this using the Power query?

Machine	Tech	Temp/Speed Wedge	Temp/Speed Extruder	Peel1	Peel2	Peel3	Peel4	Peel5	Shear1	Shear2	Shear3	Shear4	Shear5	P/F	QC	Material
M2116	AD	860/700		105	116	123	127	125	162	161	153	155	148	Pass	LB	S/S
				129	120	124	116	126
M2116	AD	860/500		126	119	124	128	121	156	140	134	158	160	Pass	LB	T/T
				125	121	115	120	115
M89	JS	860/650		121	114	115	121	113	133	155	146	156	158	Pass	LB	S/S
				125	118	122	117	126
X2102	JC		500/480	127	129	128	114	119	154	153	149	137	133	Pass	LB	EXT
M89	JS	860/550		118	120	135	124	124	153	154	151	152	147	Pass	LB	S/T

[YalanWu_test]

Hi, @rubenamn ;

You also could create a custom column :

= 
Table.Group(#"Filled Down", {"Machine", "Tech", "P/F", "QC", "Material"}, {{"Temp/Speed Wedge", each List.Max([#"Temp/Speed Wedge"]), type nullable text}, {"Temp/Speed Extruder", each List.Max([#"Temp/Speed Extruder"]), type nullable text}, {"Peel1", each  Text.Combine(  Table.Sort(_,{
{"Peel1", Order.Ascending}})[Peel1],"-") , type text},
{"Peel2", each  Text.Combine(  Table.Sort(_,{{"Peel2", Order.Ascending}})[Peel2],"-") , type text},
{"Peel3", each  Text.Combine(  Table.Sort(_,{{"Peel3", Order.Ascending}})[Peel3],"-") , type text},
{"Peel4", each  Text.Combine(  Table.Sort(_,{{"Peel4", Order.Ascending}})[Peel4],"-") , type text},
{"Peel5", each  Text.Combine(  Table.Sort(_,{{"Peel5", Order.Ascending}})[Peel5],"-") , type text}})

The final show:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVJBisMwDPxK8bkQy7Fs59jd7SVsoZAeCqGHfUP/DyuPzMq0WWghmggpGY/GWld3CkTJ7d3hS6AkP2TvJdtJkOeKaFMYgRmIegpAqsjoMuqxCJ5/7nd5fX8ILMPibvvVSaoPyMME9MDYHZTaF/9HJXvSzaYbFESTUYcCVK3oRhw8osvoJv+g+zJctnSzURHbDMhf0F2qrHlpqhObaqWMHbFW4O3YO5xsDn7Rbb1KtQEXR/kNt6+BfP1r/lROMXuIxXcrMZnNbQRUONp6RFTG/DdOL/t43TD7/diwmM3iNr9ePXfr0alsinVV4FTMTxaL1tsv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machine = _t, Tech = _t, #"Temp/Speed Wedge" = _t, #"Temp/Speed Extruder" = _t, Peel1 = _t, Peel2 = _t, Peel3 = _t, Peel4 = _t, Peel5 = _t, Shear1 = _t, Shear2 = _t, Shear3 = _t, Shear4 = _t, Shear5 = _t, #"P/F" = _t, QC = _t, Material = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Machine", "Tech", "Temp/Speed Wedge", "Temp/Speed Extruder", "Peel1", "Peel2", "Peel3", "Peel4", "Peel5", "Shear1", "Shear2", "Shear3", "Shear4", "Shear5", "P/F", "QC", "Material"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"Machine", "Tech", "Temp/Speed Wedge", "Temp/Speed Extruder", "Peel1", "Peel2", "Peel3", "Peel4", "Peel5", "Shear1", "Shear2", "Shear3", "Shear4", "Shear5", "P/F", "QC", "Material"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Replaced Value1", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Filled Down" = Table.FillDown(#"Removed Blank Rows",{"Machine", "Tech", "P/F", "QC", "Material"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Machine", "Tech", "P/F", "QC", "Material"}, {{"Temp/Speed Wedge", each List.Max([#"Temp/Speed Wedge"]), type nullable text}, {"Temp/Speed Extruder", each List.Max([#"Temp/Speed Extruder"]), type nullable text}, {"Peel1", each  Text.Combine(  Table.Sort(_,{
{"Peel1", Order.Ascending}})[Peel1],"-") , type text},
{"Peel2", each  Text.Combine(  Table.Sort(_,{{"Peel2", Order.Ascending}})[Peel2],"-") , type text},
{"Peel3", each  Text.Combine(  Table.Sort(_,{{"Peel3", Order.Ascending}})[Peel3],"-") , type text},
{"Peel4", each  Text.Combine(  Table.Sort(_,{{"Peel4", Order.Ascending}})[Peel4],"-") , type text},
{"Peel5", each  Text.Combine(  Table.Sort(_,{{"Peel5", Order.Ascending}})[Peel5],"-") , type text}})
in
    #"Grouped Rows"

Best Regards,

[AlexisOlson · Accepted solution]

Is this (a subset of) what you're after?

Here's the full code I used:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVJBisMwDPxK8bkQy7Fs59jd7SVsoZAeCqGHfUP/DyuPzMq0WWghmggpGY/GWld3CkTJ7d3hS6AkP2TvJdtJkOeKaFMYgRmIegpAqsjoMuqxCJ5/7nd5fX8ILMPibvvVSaoPyMME9MDYHZTaF/9HJXvSzaYbFESTUYcCVK3oRhw8osvoJv+g+zJctnSzURHbDMhf0F2qrHlpqhObaqWMHbFW4O3YO5xsDn7Rbb1KtQEXR/kNt6+BfP1r/lROMXuIxXcrMZnNbQRUONp6RFTG/DdOL/t43TD7/diwmM3iNr9ePXfr0alsinVV4FTMTxaL1tsv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machine = _t, Tech = _t, #"Temp/Speed Wedge" = _t, #"Temp/Speed Extruder" = _t, Peel1 = _t, Peel2 = _t, Peel3 = _t, Peel4 = _t, Peel5 = _t, Shear1 = _t, Shear2 = _t, Shear3 = _t, Shear4 = _t, Shear5 = _t, #"P/F" = _t, QC = _t, Material = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine", type text}, {"Tech", type text}, {"Temp/Speed Wedge", type text}, {"Temp/Speed Extruder", type text}, {"Peel1", Int64.Type}, {"Peel2", Int64.Type}, {"Peel3", Int64.Type}, {"Peel4", Int64.Type}, {"Peel5", Int64.Type}, {"Shear1", Int64.Type}, {"Shear2", Int64.Type}, {"Shear3", Int64.Type}, {"Shear4", Int64.Type}, {"Shear5", Int64.Type}, {"P/F", type text}, {"QC", type text}, {"Material", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    PeelColumns = List.Select(Table.ColumnNames(#"Integer-Divided Column"), each Text.StartsWith(_, "Peel")),
    OtherColumns = List.Select(Table.ColumnNames(#"Integer-Divided Column"), each not List.Contains(PeelColumns & {"Index"}, _)),
    PeelTransformations = List.Transform(PeelColumns, (col) => {col, each Text.Combine(List.Transform(List.Sort(List.Distinct(Table.Column(_, col))), Number.ToText),"-"), type text}),
    OtherTransformations = List.Transform(OtherColumns, (col) => {col, each List.Max(Table.Column(_, col))}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, OtherTransformations & PeelTransformations ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Machine", type text}, {"Tech", type text}, {"Temp/Speed Wedge", type text}, {"Temp/Speed Extruder", type text}, {"Shear1", Int64.Type}, {"Shear2", Int64.Type}, {"Shear3", Int64.Type}, {"Shear4", Int64.Type}, {"Shear5", Int64.Type}, {"P/F", type text}, {"QC", type text}, {"Material", type text}, {"Peel1", type text}, {"Peel2", type text}, {"Peel3", type text}, {"Peel4", type text}, {"Peel5", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Index", "Machine", "Tech", "Temp/Speed Wedge", "Temp/Speed Extruder", "Peel1", "Peel2", "Peel3", "Peel4", "Peel5", "Shear1", "Shear2", "Shear3", "Shear4", "Shear5", "P/F", "QC", "Material"})
in
    #"Reordered Columns"

I added an index to easily group the pairs of rows together. During the group by on the index column, I took the maximum over all of the non-index, non-Peel columns. For the Peel columns, I combined the distinct values using "-" as a separator.

[wdx223_Daniel]

NewStep=Table.FromRecords(Table.Group(PreviousStepName,"Machine",{"n",each let a=Table.ColumnNames(_) in Record.SelectFields(_{0},List.FirstN(a,4))&Record.Combine(List.Transform(List.Skip(a,4),(x)=>Record.AddField([],x,Text.Combine(List.Transform(Table.Column(_,x),Text.From),"-"))))},0,(x,y)=>Byte.From(y<>null))[n])