Transposing rows with specific values to columns

[jcecil2]

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

[v-shex-msft · Accepted solution]

HI @jcecil2 ,

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

[Greg_Deckler]

Looks like a job for @ImkeF