Changing long table to wide table in PQ

Power Query
1

Hi,
I am working on developing some sort of mechanism to ease the teachers in their student assessment work. For this I have to use macros, power query in excel workbook that would do all reporting tasks with a click. Due to dynamic nature and having many possibilities, I am facing some difficulties.
Need to use of back-end operation in Power Query to assembling data into wide table as previously done in pivot tables as shown in figure.

Wide Tables
Wide Tables1494Ă—385 13.9 KB

Result Table.xlsx (34.0 KB)
Result Table.pbix (27.5 KB)

I couldn’t solve to get desired tables in PQ in a straight way by pivoting.
Also, it is required to populate table separately and in consolidated form in MS Excel without any interference of user.

2

Hi @Tanzeel,

Try this.
Just copy the M script into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZrBalRBEEX/ZdYJTHfXVL23DroyqOuQheAmIP7/UjIztx4KwXs2hsBR8HC51a+ol5fT2B6fPz2O08Pp+e3n9/efT1++Xn//dv/59P5HnV4fBF8IrH953uF5h6+/zw/gReC4w+sOX//y+gC+EDgJXHc4/gd/fvv941fL++u/+C8u05Y8wZY8wZY8wZY8wZY8wZa8C5M3ziinor2givaSKtqLqmgvq6K9sIr20nqGxlGNDFYNrBtYObB2YPXA+gEWxJjIuGjPuGjPuGjPuGjPuGjPuGjP+ITGd2RctGdctGdctGdctGdctGdctGd8Z8Yn6vGJenyiHp+oxyfq8Yl6fKIen7DHZyHjoj3joj3joj3joj3joj3joj3jxYwv1ONNW8abtow3bRlv2jLetGW8acv4gj0e6K3StGW8act405bxpi3jTVvGm7aMB3yrRCDjoj3joj3joj3joj3joj3joj3jAY2jHg/U44F6PFCPB+rxQD0eqMcD9vhlEeNNe9/3oi3jTXtf+KK9T3zR3je+aO8jf0HjiYyL9oyL9oyL9oyL9oyL9oyL9ownM54o401bxpu2jDdtGW/aMt60Zbxpy3jCjCfauCbaGiZaGybaGyZaHCbaHCZaHSbcHSaanIkmZ6LJmWhyJpqciSZnosmZcHIm2qsk2qsk2qsk2qsk2qsk2qsk2qsk3KsU2qsU2qsU2qsU2qsU2qsU2qsU2qsU3KsUeqsUeqsUeqsUeqsUeqsUeqsUeqsUfKsUapVCrVKoVQq1SqFWKdQqhVqlYKts6K2yobfKht4qG3qrbOitsqG3yobeKht8q+zak1nGm7aMN20Zb9oy3rRlvGnLeNOW8StNjKNNVtOecbTJatozjjZZTXvG0SZrh5usHbXKjlplR62yo1bZUavsqFV21Co7bZUNGRftGRftGRftGRftGRftGRftGd+Y8XFmh1VnlPIDt6QfuHmzgoJ+4ObVCor6DUd3K+xUCN4KwWMheC0Ez4XgvRA8GOIXQyzzg2V+sMwPlvnBMj9Y5gfL/MCZRx+jB26aR5+jB26aRx+kB26aR5+kNxyZR6P1wE3zaLgeuGkejdcDN82jAXvDkXl2vAWvt+D5Frzfggdc8IILnnDRG66xWOYXy/ximV8s84tlfrHML5b5RTMfbMIGm7DBJmywCRtswgabsMEmbNAJGyzzwTIfLPPBMh8s88EyHyzz4WX+9Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RegNo = _t, QuestionType = _t, CLO = _t, PLO = _t, Taxonomy = _t, Value = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    GroupRows = Table.Group( ChType, {"RegNo"}, {
        {"AllRows", each 
            let 
                ToList = Table.ToColumns( _ ), 
                ToTable = Table.FromColumns( {List.Repeat( ToList{0}, 3), List.Combine({ToList{2}, ToList{3}, ToList{4}}), List.Repeat( ToList{5}, 3) }) 
            in  
                Table.Pivot( ToTable, List.Distinct(ToTable[Column2]), "Column2", "Column3", List.Sum) }
        }),
    ExpandAllRows = Table.ExpandTableColumn( GroupRows, "AllRows", 
        {"CLO-1", "CLO-2", "CLO-3", "CLO-4", "PLO-1", "PLO-2", "PLO-3", "C1", "C2", "C3"}, 
        {"CLO-1", "CLO-2", "CLO-3", "CLO-4", "PLO-1", "PLO-2", "PLO-3", "C1", "C2", "C3"}
    )
in
    ExpandAllRows

.
With this result

image
image1257Ă—236 5.46 KB

Here’s your sample file.
Result Table.pbix (34.8 KB)

I hope this is helpful.

2 Likes
3

Thank you very much.
However, I need maximum flexibility as there might be any PLO attribute from (1-12) as well as CLOs. I just tried an extra column “Assessment” and it didn’t respond.

Wide Tables-1
Wide Tables-11435Ă—609 42.8 KB

Result Table (1).pbix (36.4 KB)

May we get any dynamic solution?

4

Please be more specific on your requirement…
How should additional columns be handled? Are they attributes like the RegNo?

If it’s just the expansion of the nested table, try this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZrBalRBEEX/ZdYJTHfXVL23DroyqOuQheAmIP7/UjIztx4KwXs2hsBR8HC51a+ol5fT2B6fPz2O08Pp+e3n9/efT1++Xn//dv/59P5HnV4fBF8IrH953uF5h6+/zw/gReC4w+sOX//y+gC+EDgJXHc4/gd/fvv941fL++u/+C8u05Y8wZY8wZY8wZY8wZY8wZa8C5M3ziinor2givaSKtqLqmgvq6K9sIr20nqGxlGNDFYNrBtYObB2YPXA+gEWxJjIuGjPuGjPuGjPuGjPuGjPuGjP+ITGd2RctGdctGdctGdctGdctGdctGd8Z8Yn6vGJenyiHp+oxyfq8Yl6fKIen7DHZyHjoj3joj3joj3joj3joj3joj3jxYwv1ONNW8abtow3bRlv2jLetGW8acv4gj0e6K3StGW8act405bxpi3jTVvGm7aMB3yrRCDjoj3joj3joj3joj3joj3joj3jAY2jHg/U44F6PFCPB+rxQD0eqMcD9vhlEeNNe9/3oi3jTXtf+KK9T3zR3je+aO8jf0HjiYyL9oyL9oyL9oyL9oyL9oyL9ownM54o401bxpu2jDdtGW/aMt60Zbxpy3jCjCfauCbaGiZaGybaGyZaHCbaHCZaHSbcHSaanIkmZ6LJmWhyJpqciSZnosmZcHIm2qsk2qsk2qsk2qsk2qsk2qsk2qsk3KsU2qsU2qsU2qsU2qsU2qsU2qsU2qsU3KsUeqsUeqsUeqsUeqsUeqsUeqsUeqsUfKsUapVCrVKoVQq1SqFWKdQqhVqlYKts6K2yobfKht4qG3qrbOitsqG3yobeKht8q+zak1nGm7aMN20Zb9oy3rRlvGnLeNOW8StNjKNNVtOecbTJatozjjZZTXvG0SZrh5usHbXKjlplR62yo1bZUavsqFV21Co7bZUNGRftGRftGRftGRftGRftGRftGd+Y8XFmh1VnlPIDt6QfuHmzgoJ+4ObVCor6DUd3K+xUCN4KwWMheC0Ez4XgvRA8GOIXQyzzg2V+sMwPlvnBMj9Y5gfL/MCZRx+jB26aR5+jB26aRx+kB26aR5+kNxyZR6P1wE3zaLgeuGkejdcDN82jAXvDkXl2vAWvt+D5Frzfggdc8IILnnDRG66xWOYXy/ximV8s84tlfrHML5b5RTMfbMIGm7DBJmywCRtswgabsMEmbNAJGyzzwTIfLPPBMh8s88EyHyzz4WX+9Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RegNo = _t, QuestionType = _t, CLO = _t, PLO = _t, Taxonomy = _t, Value = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    GroupRows = Table.Group( ChType, {"RegNo"}, {
        {"AllRows", each 
            let 
                ToList = Table.ToColumns( _ ), 
                ToTable = Table.FromColumns( {List.Repeat( ToList{0}, 3), List.Combine({ToList{2}, ToList{3}, ToList{4}}), List.Repeat( ToList{5}, 3) }) 
            in  
                Table.Pivot( ToTable, List.Distinct(ToTable[Column2]), "Column2", "Column3", List.Sum) }
        }),
    ExpandAllRows = Table.ExpandTableColumn( GroupRows, "AllRows", 
        List.Skip( Table.ColumnNames( GroupRows{0}[AllRows] ), 1 ), 
        List.Skip( Table.ColumnNames( GroupRows{0}[AllRows] ), 1 )
    )
in
    ExpandAllRows
3 Likes
5

Alternatively.
This solution which doesn’t rely on source column order AND column names in the first nested table for the expand but does require the presence of columns: RegNo, CLO, PLO and Taxonomy

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZrBalRBEEX/ZdYJTHfXVL23DroyqOuQheAmIP7/UjIztx4KwXs2hsBR8HC51a+ol5fT2B6fPz2O08Pp+e3n9/efT1++Xn//dv/59P5HnV4fBF8IrH953uF5h6+/zw/gReC4w+sOX//y+gC+EDgJXHc4/gd/fvv941fL++u/+C8u05Y8wZY8wZY8wZY8wZY8wZa8C5M3ziinor2givaSKtqLqmgvq6K9sIr20nqGxlGNDFYNrBtYObB2YPXA+gEWxJjIuGjPuGjPuGjPuGjPuGjPuGjP+ITGd2RctGdctGdctGdctGdctGdctGd8Z8Yn6vGJenyiHp+oxyfq8Yl6fKIen7DHZyHjoj3joj3joj3joj3joj3joj3jxYwv1ONNW8abtow3bRlv2jLetGW8acv4gj0e6K3StGW8act405bxpi3jTVvGm7aMB3yrRCDjoj3joj3joj3joj3joj3joj3jAY2jHg/U44F6PFCPB+rxQD0eqMcD9vhlEeNNe9/3oi3jTXtf+KK9T3zR3je+aO8jf0HjiYyL9oyL9oyL9oyL9oyL9oyL9ownM54o401bxpu2jDdtGW/aMt60Zbxpy3jCjCfauCbaGiZaGybaGyZaHCbaHCZaHSbcHSaanIkmZ6LJmWhyJpqciSZnosmZcHIm2qsk2qsk2qsk2qsk2qsk2qsk2qsk3KsU2qsU2qsU2qsU2qsU2qsU2qsU2qsU3KsUeqsUeqsUeqsUeqsUeqsUeqsUeqsUfKsUapVCrVKoVQq1SqFWKdQqhVqlYKts6K2yobfKht4qG3qrbOitsqG3yobeKht8q+zak1nGm7aMN20Zb9oy3rRlvGnLeNOW8StNjKNNVtOecbTJatozjjZZTXvG0SZrh5usHbXKjlplR62yo1bZUavsqFV21Co7bZUNGRftGRftGRftGRftGRftGRftGd+Y8XFmh1VnlPIDt6QfuHmzgoJ+4ObVCor6DUd3K+xUCN4KwWMheC0Ez4XgvRA8GOIXQyzzg2V+sMwPlvnBMj9Y5gfL/MCZRx+jB26aR5+jB26aRx+kB26aR5+kNxyZR6P1wE3zaLgeuGkejdcDN82jAXvDkXl2vAWvt+D5Frzfggdc8IILnnDRG66xWOYXy/ximV8s84tlfrHML5b5RTMfbMIGm7DBJmywCRtswgabsMEmbNAJGyzzwTIfLPPBMh8s88EyHyzz4WX+9Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RegNo = _t, QuestionType = _t, CLO = _t, PLO = _t, Taxonomy = _t, Value = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    GroupRows = Table.Group( ChType, {"RegNo"}, {
        {"AllRows", each 
            let 
                ToTable = Table.FromColumns( { List.Combine({ [CLO], [PLO], [Taxonomy] }), List.Repeat( [Value], 3) }),
                Pivot = Table.Pivot( ToTable, List.Distinct( ToTable[Column1] ), "Column1", "Column2", List.Sum )
            in  
                Pivot }
        }),
    ExpandAllRows = Table.ExpandTableColumn( GroupRows, "AllRows", 
        List.Distinct( List.Combine({ ChType[CLO], ChType[PLO], ChType[Taxonomy] }) )
    )
in
    ExpandAllRows

I hope this is helpful

2 Likes
6

Thank you very much. It really solved the issue.
I just added [Assessment] in the code.

`let
Source = Excel.Workbook(File.Contents("C:\Users\Tanzeel\Desktop\Result Table.xlsx"), null, true),
Result_Table = Source{[Item="Result",Kind="Table"]}[Data],
ChType = Table.TransformColumnTypes(Result_Table,{{"Value", Int64.Type}}),
GroupRows = Table.Group( ChType, {"RegNo"}, {
    {"AllRows", each 
        let 
            ToTable = Table.FromColumns( { List.Combine({ [CLO], [PLO], [Taxonomy], [Assessment] }), List.Repeat( [Value], 4) }),
            Pivot = Table.Pivot( ToTable, List.Distinct( ToTable[Column1] ), "Column1", "Column2", List.Sum )
        in  
            Pivot }
    }),
ExpandAllRows = Table.ExpandTableColumn( GroupRows, "AllRows", 
    List.Distinct( List.Combine({ ChType[CLO], ChType[PLO], ChType[Taxonomy], ChType[Assessment] }) )
)

in
ExpandAllRows`

1 Like