@joooffice here's my take on it:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLBjoIwEIZfxXD2IC1d26OihyVrYsSDxngYtQmNSDcgJr69M4FDQWxsevjSaeef/jOHQ8AnP8E4YEKwKFJI6V3/Z7og0lVlbDGaObxDjqG0uSm058qsynTpiacZeN4fx1SWaMua8inSMofPknvkuT15BPu7kYhaCRlKpBWUJn+6P587vEFObOaG4154WIQ3Inwiyei1vneccSW2JAHnq9FNAvd8ODdrP6AYuZXUuYGvClzAw1w8FxPiM3TL2LeiIZ4JJXGpppV1X9O3KQdT9JJHUgkuB8pZvJnSaX0/PCxBiYXkIS6kX/jUWBqPrbl5on+2NpVv3FO4da3CGT6+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Submission ID" = _t, #"Account ID" = _t, #"Adult 1 Name" = _t, #"Adult 1 Day 1 Session" = _t, #"Adult 1 Day 2 Session" = _t, #"Adult 2 Name" = _t, #"Adult 2 Day 1 Session" = _t, #"Adult 2 Day 2 Session" = _t, #"Child 1 Name" = _t, #"Child 1 Day 1 Session" = _t, #"Child 1 Day 2 Session" = _t, #"Child 2 Name" = _t, #"Child 2 Day 1 Session" = _t, #"Child 2 Day 2 Session" = _t]),
#"Unpivoted All but Submission ID & Account ID" = Table.UnpivotOtherColumns(Source, {"Submission ID", "Account ID"}, "Attribute", "Value"),
#"Remove nulls" = Table.SelectRows(#"Unpivoted All but Submission ID & Account ID", each ([Value] <> "")),
#"Filter Just Names" = Table.SelectRows(#"Remove nulls", each Text.EndsWith([Attribute], "Name")),
#"Renamed Columns" = Table.RenameColumns(#"Filter Just Names",{{"Attribute", "Title"}, {"Value", "Name"}}),
#"Final Name List" = Table.TransformColumns(#"Renamed Columns", {{"Title", each Text.BeforeDelimiter(_, " Name"), type text}}),
#"Filter Just Sessions" = Table.SelectRows(#"Remove nulls", each not Text.EndsWith([Attribute], "Name")),
#"Replaced Value" = Table.ReplaceValue(#"Filter Just Sessions"," Session","",Replacer.ReplaceText,{"Attribute"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Attribute", Splitter.SplitTextByEachDelimiter({" Day "}, QuoteStyle.Csv, false), {"Title", "Day"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Title", type text}, {"Day", Int64.Type}}),
#"Final Session List" = Table.RenameColumns(#"Changed Type",{{"Value", "Session"}}),
#"Merge Names into Sessions" = Table.NestedJoin(#"Final Session List", {"Submission ID", "Account ID", "Title"}, #"Final Name List", {"Submission ID", "Account ID", "Title"}, "NameList", JoinKind.LeftOuter),
#"Expand Names" = Table.ExpandTableColumn(#"Merge Names into Sessions", "NameList", {"Name"}, {"Name"}),
#"Merged Queries" = Table.NestedJoin(#"Expand Names", {"Submission ID", "Account ID", "Day", "Session"}, #"Expand Names", {"Submission ID", "Account ID", "Day", "Session"}, "Expand Names", JoinKind.LeftOuter),
#"Aggregated Expand Names" = Table.AggregateTableColumn(#"Merged Queries", "Expand Names", {{"Title", each List.Sort(_), "List of Titles"}}),
#"Added Custom" = Table.AddColumn(#"Aggregated Expand Names", "Name Column Header", each "Name " & Text.From(List.PositionOf([List of Titles],[Title])+1), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Title", "List of Titles"}),
#"Pivot Titles" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Name Column Header]), "Name Column Header", "Name")
in
#"Pivot Titles"
The query is in two main sections, producing:
- "Final Name List" - a list of names for each Submission/Account
- "Final Session List" - a list of sessions for each Submission/Account/Title (Title being Adult 1 etc)
These are then merged and unpivoted.
I also included a Day column which is an integer (1 or 2 in the sample data).
Does this work for you?
Regards,
Owen
