Forum Discussion
Transposing rows with specific values to columns
Hello,
I am attempting to perform some ETL steps on some data that I have. The table is in a pseudo-stacked form as exemplified below:
| Name | ID | Total | Time |
| John Smith | 1111 | 7 | 8.60 |
| Long Jumps | NULL | 4 | 7.93 |
| Sprints | NULL | 2 | 0.28 |
| Relays | NULL | 1 | 0.38 |
| TOTALS | NULL | 7 | 8.60 |
| Aaron Jones | 1112 | 5 | 6.00 |
| Relays | NULL | 1 | 0.77 |
| Discus | NULL | 2 | 4.98 |
| Sprints | NULL | 2 | 0.25 |
| TOTALS | NULL | 5 | 6.00 |
I am attempting to transpose rows with event names to columns with count values from Totals column. The outcome I am trying to achieve is below:
| Name | ID | Total | Time | Long Jumps - Total | Long Jumps - Time | Sprints - Total | Sprints - Times | Relays - Total | Relays - Time | Discus - Total | Discus - Time |
| John Smith | 1111 | 7 | 8.60 | 4 | 7.93 | 2 | 0.28 | 1 | 0.38 | NULL | NULL |
| Aaron Jones | 1112 | 5 | 6.00 | NULL | NULL | 2 | 0.25 | 1 | 0.77 | 2 | 4.98 |
The issue I can't seem to work through is that not everyone will participate in every event, so the rows between each person can vary in number. Does anyone know how to achieve the second table above?
Thank you
HI Anonymous ,
You can try to use the following M query formula if it suitable for your requirement.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTIEAiBlDsQWemZKsTrRSj75eekKXqW5BcVAQb9QHx8gZQJSo2dpDFYQXFCUmVeCJGsExAZ6RhZg2aDUnMRKJElDsKQxRDLEP8TRJxghiWyvY2JRfp6CV35eajHEYSBjTYHYDI+55uZgSZfM4uRSNBeZ6Fla4HWvKVYnQW2MBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, Total = _t, Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", type number}, {"Total", Int64.Type}, {"Time", type number}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"ID"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"ID"}, {{"Contents", each _, type table [Name=text, ID=number, Total=number, Time=number]}}), ExtractName = Table.AddColumn(#"Grouped Rows", "Name", each [Contents]{0}[Name]), ExtractTotalValue = Table.AddColumn(ExtractName, "Score and Time", each Text.From([Contents]{0}[Total])&","&Text.From([Contents]{0}[Time])), GetList = Table.AddColumn(ExtractTotalValue, "RecordList", each Text.Combine(List.RemoveLastN(List.RemoveFirstN([Contents][Name],1),1),",")), AddColumnAndTransform = Table.AddColumn(GetList, "Transform", each Table.PromoteHeaders(Table.Transpose(Table.CombineColumns(Table.TransformColumnTypes(Table.UnpivotOtherColumns(Table.AddIndexColumn(Table.RemoveLastN(Table.Skip( Table.SelectColumns([Contents],{"Total","Time"}),1),1), "Index", 1, 1), {"Index"}, "Attribute", "Value"), {{"Index", type text}}, "en-US"),{"Attribute","Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")), [PromoteAllScalars=true])), #"Expanded Transform" = Table.ExpandTableColumn(AddColumnAndTransform, "Transform", {"Total 1", "Time 1", "Total 2", "Time 2", "Total 3", "Time 3"}, {"Total 1", "Time 1", "Total 2", "Time 2", "Total 3", "Time 3"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Transform",{"Contents"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Score and Time", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Total Score", "Total Time"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Total Score", Int64.Type}, {"Total Time", type number}}) in #"Changed Type1"BTW, I do not so recommend you to do complex data structure conversion on power query side. It is hard to coding formula and your need to manually update query steps formula every time your data structure changes.
Regards,
Xiaoxin Sheng
2 Replies
HI Anonymous ,
You can try to use the following M query formula if it suitable for your requirement.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUTIEAiBlDsQWemZKsTrRSj75eekKXqW5BcVAQb9QHx8gZQJSo2dpDFYQXFCUmVeCJGsExAZ6RhZg2aDUnMRKJElDsKQxRDLEP8TRJxghiWyvY2JRfp6CV35eajHEYSBjTYHYDI+55uZgSZfM4uRSNBeZ6Fla4HWvKVYnQW2MBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, Total = _t, Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"ID", type number}, {"Total", Int64.Type}, {"Time", type number}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"ID"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"ID"}, {{"Contents", each _, type table [Name=text, ID=number, Total=number, Time=number]}}), ExtractName = Table.AddColumn(#"Grouped Rows", "Name", each [Contents]{0}[Name]), ExtractTotalValue = Table.AddColumn(ExtractName, "Score and Time", each Text.From([Contents]{0}[Total])&","&Text.From([Contents]{0}[Time])), GetList = Table.AddColumn(ExtractTotalValue, "RecordList", each Text.Combine(List.RemoveLastN(List.RemoveFirstN([Contents][Name],1),1),",")), AddColumnAndTransform = Table.AddColumn(GetList, "Transform", each Table.PromoteHeaders(Table.Transpose(Table.CombineColumns(Table.TransformColumnTypes(Table.UnpivotOtherColumns(Table.AddIndexColumn(Table.RemoveLastN(Table.Skip( Table.SelectColumns([Contents],{"Total","Time"}),1),1), "Index", 1, 1), {"Index"}, "Attribute", "Value"), {{"Index", type text}}, "en-US"),{"Attribute","Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")), [PromoteAllScalars=true])), #"Expanded Transform" = Table.ExpandTableColumn(AddColumnAndTransform, "Transform", {"Total 1", "Time 1", "Total 2", "Time 2", "Total 3", "Time 3"}, {"Total 1", "Time 1", "Total 2", "Time 2", "Total 3", "Time 3"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Transform",{"Contents"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Score and Time", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Total Score", "Total Time"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Total Score", Int64.Type}, {"Total Time", type number}}) in #"Changed Type1"BTW, I do not so recommend you to do complex data structure conversion on power query side. It is hard to coding formula and your need to manually update query steps formula every time your data structure changes.
Regards,
Xiaoxin Sheng
Looks like a job for ImkeF
