Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rubenamn
New Member

Rotate mi data using power query

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?

 

 

 

 

MachineTechTemp/Speed WedgeTemp/Speed ExtruderPeel1Peel2Peel3Peel4Peel5Shear1Shear2Shear3Shear4Shear5P/FQCMaterial
M2116AD860/700 105116123127125162161153155148PassLBS/S
    129120124116126        
M2116AD860/500 126119124128121156140134158160PassLBT/T
    125121115120115        
M89JS860/650 121114115121113133155146156158PassLBS/S
    125118122117126        
X2102JC 500/480127129128114119154153149137133PassLBEXT
                 
M89JS860/550 118120135124124153154151152147PassLBS/T

 

 

 

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

AlexisOlson_0-1660155727369.png

 

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.

View solution in original post

3 REPLIES 3
YalanWu_test
Helper I
Helper I

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:

YalanWu_test_0-1660196921886.png

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
Super User
Super User

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

AlexisOlson_0-1660155727369.png

 

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
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors