Pivoting groups of columns - enumeration error

Power Query
1

Hi everyone,

I’ve been grappling with a pivot challenge… I have a spreadsheet with multiple grouped columns. 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:

image
image2419×426 30.7 KB

And the output like this:

image
image1774×932 79 KB

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)

2

Bumping this post for more visibility.

3

Hi @Melissa @BrianJ - Do check if can help here. Have tried myself but not getting to the required Output.

Thanks
Ankit J

1 Like
4

Hi @corkemp,

You can try this. Just paste the code into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZJva8IwEMa/SulrByb9k/alOtgmzAkVYYgvzjbMsJqONPr5l4tn7Wo/wMCj5nfX57lLb7cLZyVU8qTK4FOCCSdhYc+V1DZ4e3aHFZyke2xka4MNfEvtDh8XaaCu3b/ut21KOASFLK1qdD/xDvbY3hJtP7NqdLCV5gD143v7yS7s1xYWdAWmCoqyMdjP3J2JK41gLU3pmla1/F/lOAmfcvbEuUswFy+yMV9YNavgx6qLDPByMRlNkbpIXeRYy/gNCRdZ4lFEKHeReFUeE8JUngxMMdZwrscsfcGc3vTyUUYk6+RZSii+o5gQdhmLgSOKLZujHnNkqLtwgZOJuLNckCUT3dwLmlukviomhJZJNLDEZAEGjqNTotorqXkxxohgq2nu9QWh++1HOSF27eKvJV7XUratKmH0a2JLG2otv7qKHsr8oFFKiN2vgxPCZ84Hrli/PNdq1JPd1sXvhp+B8d5uDNbFf/BrZ/2lyuJwv/8F", 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]),
    TransposeTable = Table.Transpose(Source),
    ReplaceValue = Table.ReplaceValue(TransposeTable,"",null,Replacer.ReplaceValue,{"Column1"}),
    FillDown = Table.FillDown(ReplaceValue,{"Column1"}),
    MergeColumns = Table.CombineColumns(FillDown,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Header"),
    TransposeBack = Table.Transpose(MergeColumns),
    PromoteHeader = Table.PromoteHeaders(TransposeBack, [PromoteAllScalars=true]),
    AddTempCol = Table.AddColumn( PromoteHeader, "Temp|", each 
        List.Zip( 
            { 
                { "Overall", "Vocab", "Maths", "Non-verbal" },
                Record.ToList( Record.SelectFields( _, List.Select( Table.ColumnNames( PromoteHeader ), each Text.EndsWith( _, "Standard Score", Comparer.OrdinalIgnoreCase )))),
                Record.ToList( Record.SelectFields( _, List.Select( Table.ColumnNames( PromoteHeader ), each Text.EndsWith( _, "Band", Comparer.OrdinalIgnoreCase )))),
                Record.ToList( Record.SelectFields( _, List.Select( Table.ColumnNames( PromoteHeader ), each Text.EndsWith( _, "Stanine", Comparer.OrdinalIgnoreCase )))),
                Record.ToList( Record.SelectFields( _, List.Select( Table.ColumnNames( PromoteHeader ), each Text.EndsWith( _, "Percentile", Comparer.OrdinalIgnoreCase ))))
            }
        )),
    NewTable = Table.ExpandListColumn( Table.SelectColumns( AddTempCol, List.Select( Table.ColumnNames( AddTempCol), each Text.EndsWith( _, "|" )) ), "Temp|" ),
    ExtractValues = Table.TransformColumns(NewTable, {"Temp|", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    SplitColumn = Table.SplitColumn(ExtractValues, "Temp|", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"CEM category", "Standardised Score", "Band", "Stanine", "Percentile"}),
    ChType = Table.TransformColumnTypes(SplitColumn,{{"Standardised Score", Int64.Type}, {"Stanine", Int64.Type}, {"Percentile", Int64.Type}}),
    RenameCols = Table.RenameColumns( ChType, List.Zip( { Table.ColumnNames(ChType), List.Transform( Table.ColumnNames(ChType), each Text.TrimEnd(_, "|")) } ))
in
    RenameCols

.
I hope this is helpful.

3 Likes
5

Hello @corkemp, just following up if the responses above help you solve your inquiry?

We’ve noticed that no response has been received from you since a few days ago. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

I’m not entirely sure that actually works, but it does and it’s great!

Thank you.
Matt