Transform multiple grouped rows and columns to flat table

[corkemp]

Hi everyone,

I have a messy table that looks like below identifying exams on particular days and their durations. It works well to help check exam clashes but is a pain to quickly turn into a neat timetable of exams.

Each day has three exam sessions so 1 day has three columns. And each exam session could have multiple different exams taking place. For each exam the rows are repeated to specify the subject, exam paper name, start time, end time and end time (+25%).

I would like to transform this so that I have a flat table with date, subject, exam paper name, start time, end time and end time (+25%) as column headings.

The few approaches I’ve managed to find deal with grouped columns but not grouped columns and rows. Any help would be much appreciated.

Many thanks
Matt

PowerQuery Transformation Help.xlsx (24.1 KB)

[Melissa]

Hi @corkemp,

Paste this M code into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVZLb9swDP4rhoEBG5aDHlaS5bj0sUMLBMthh6IHFzFaD4lTuMk27NdPsiyalGQn8YYVaix/oviJFB9+eEjTSbrLJwlnyfe80i92bEoNcQL93GtIUCkDSQL9qDWUEei3Ua8I1DDOQsZ5yPgJQY+T5rzc/IsFY+aZLbhqMaH/JZoPrV0+N+TXv/KdfvlSwhmv27MtX/TPVaF/PpcObsRudrlbXoHMrflZv4KWm9psfHMyq2u3vHx1W5z9q/y1qJMq3xnQvnDQY8e6rJ63RUIWCSZ6pUXP9saa55eiXiR35duhqLRMklebpC7yjZ4HUt/q8gB4jDwqiOy8Otb5odwbPzJz1/a67aAA44tM2adD9ZS3T6XoBg3J9imY2w1L/tMtARMPZZ0atMQVZeJITXOJ2+NbItQ7ezTpG4cA/cY7fYhCzFrtYiFk64BupzFcAH3m0D5lMO0EwGIpwb1oR4bc2QmgKaJ3qLN+fcjrQ3IomxDm/uVSoJm2qY4uEaEZHCsucFJDXCA4zL9R2VQRnTfO+swnJACKQC5pdLfotJtmWAAFgmCggUR6MEUC7WEkjfeYylgUdfRIwLc+ef9RZ8AHe3LpOyGeApTPpYB2gpzbEEM74ylgwmUwBZAAOGE4BahAJAWQADjBNpKmvH9dp21HSLtWkraNxI6rOsU9xCF+B0Hra9Bsx31+bvdY7ndPZVVsaAPwUDGwQ/QqMefcH6tNU9hPdRIsSZpE/Ci94sjruKeQFCFPSD3OgqnbiVCUjXRVME8fTWyknXQSCHqN+r2ChHQkeEGjS43gLdACfcTfFEme7uwKW0L3RTIETLIuH+oBeJr5djHiQvJGgd76e2atHyLr5/Vru8RlEGhVeFvcCwxvGQUq7xT6QQcLNO4QdYwsUtRRAPpWoZotcXmD4qn8j5kxoWhsiIaiaVC+cX8fim0t1uPOfKrfH0HszHFb9y4GNXfcGPpYHihy7opdiFxKG1SWqHldfQq87i7uUlbcLuOspH6kw3XjzBEUhBgvyu/Uz+tsFKs8w8dh/tGd+guIzUa42vsy6SGH/FhuLyVI/0d2nGr9PqWXIWzIAf2DNvQ+62hysIWa4m/Li9hOh8lAUlw6xiSDGJcBxrqJyvjU/M3OIqR5IMZ7daKUYHOp//p5H/8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t, Column27 = _t, Column28 = _t]),
    TransposeTable = Table.Transpose(Source),
    ReplaceBlanksWithNull = Table.ReplaceValue(TransposeTable,"",null,Replacer.ReplaceValue,{"Column1"}),
    FillDown = Table.FillDown(ReplaceBlanksWithNull,{"Column1"}),
    PromoteHeaders = Table.PromoteHeaders(FillDown, [PromoteAllScalars=true]),
    GetValues = Table.AddColumn(PromoteHeaders, "Custom", each
        List.Zip(
            {
                List.ReplaceMatchingItems( Record.ToList( Record.SelectFields(_, List.Select( Table.ColumnNames(PromoteHeaders), each Text.StartsWith( _, "Exam", Comparer.OrdinalIgnoreCase )))), {{null, ""}}),
                List.ReplaceMatchingItems( Record.ToList( Record.SelectFields(_, List.Select( Table.ColumnNames(PromoteHeaders), each Text.StartsWith( _, "Paper", Comparer.OrdinalIgnoreCase )))), {{null, ""}}),
                List.ReplaceMatchingItems( Record.ToList( Record.SelectFields(_, List.Select( Table.ColumnNames(PromoteHeaders), each Text.StartsWith( _, "Start", Comparer.OrdinalIgnoreCase )))), {{null, ""}}),
                List.ReplaceMatchingItems( Record.ToList( Record.SelectFields(_, List.Difference( List.Select( Table.ColumnNames(PromoteHeaders), each Text.StartsWith( _, "End Time", Comparer.OrdinalIgnoreCase )), List.Select( Table.ColumnNames(PromoteHeaders), each Text.StartsWith( _, "End time ", Comparer.OrdinalIgnoreCase ))))), {{null, ""}}),
                List.ReplaceMatchingItems( Record.ToList( Record.SelectFields(_, List.Select( Table.ColumnNames(PromoteHeaders), each Text.StartsWith( _, "End time ", Comparer.OrdinalIgnoreCase )))), {{null, ""}})
            }
        )
    )[[Column1], [Custom]],
    ExpandValues = Table.ExpandListColumn(GetValues, "Custom"),
    ExtractValues = Table.TransformColumns(ExpandValues, {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    SelectRows = Table.SelectRows( ExtractValues, each Text.Length( [Custom] ) <>4 ),
    SplitColumn = Table.SplitColumn(SelectRows, "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5"}),
    RenameCols = Table.RenameColumns(SplitColumn,{{"Column1", "Date"}, {"Custom.1", "Subject"}, {"Custom.2", "Paper name"}, {"Custom.3", "Start time"}, {"Custom.4", "End time"}, {"Custom.5", "End time (+25%)"}})
in
    RenameCols

.
Snip of the result

I hope this is helpful

[EnterpriseDNA]

Thank you so much for the detailes answer @Melissa.

Hi @corkemp, welcome back to the forum. Good to see that you are active again

We’ve noticed that no response has been received from you since October 19th.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[corkemp]

Hi Melissa,

Thanks so much! That worked perfectly.

Any chance you could explain or point me in the direction of the technique to do this? I have a few other instances in which it’d be useful, especially working with groups of columns.

Thank you
Matt

[Melissa]

Hi @corkemp,

Glad to hear this worked well for you
This solution is specific to the lay-out you had so if those other instances share that set-up you can use this technique. Wrote the core part of it by hand so no UI to help you there but if your read the code I’m confident you’ll be able to translate that to any future requirement.

I’ll describe some key parts ReplaceBlanksWithNull step: Fill Down only works on actual “null” values. GetValues step: this first, nested line get’s the “Subject” values

List.ReplaceMatchingItems( 
    Record.ToList( 
        Record.SelectFields( _, // underscore returns a record for the current row of the table
            List.Select( 
                Table.ColumnNames(PromoteHeaders), // initial list with all column (or field) names in the table
                each Text.StartsWith( _, "Exam", Comparer.OrdinalIgnoreCase ) // keep column names that start with "Exam" ignoring case
            )  // Select fields in the record based on the above criteria
        )  // Transform the record with selected fields into a list of field values
    ), {{null, ""}} // In this field values list replace nulls with blanks (empty text strings)
)

This basic pattern is repeated to get the other values as well. Once that is done we are paring the items in each list by position (row) using List.Zip and end up with a single list for all the values for each row (record) that we can expand- and extract.

I highly recommend to rebuilt it yourself using “Add custom column” to split each of the steps in the GetValues transformation up so you can ‘see’ the result of each individual function used.

This chapter in the Power Query M course can help you resolve errors you might encounter.

How To Successfully Implement A Piece Of M Code

I hope this is helpful.

[corkemp]

Thanks, Melissa. I’ve been grappling with another pivot challenge… I can make it work in Excel, but when I do it in a dataflow an error is produced upon refresh ( “Too many elements in the enumeration to complete…”).

The structure for input looks like this:

And the output like this:

I’m not sure why it isn’t working… I’ve tried grouping and adding an index before pivoting but no luck.

Any help would be much appreciated.

Thank you
Matt
DEMODATA.xlsx (41.1 KB)

[Keith]

please start a new posting …one question per posting…against the rules once a posting is solved.

thanks