Unpivot more than once

[Hamdan1234]

Hello,

I have below table. In this table each column course1,2 and 3 are representing dates of course completion , similarly same columns are repeating showing Yes and No and similar columns are representing compliance of each student for every course. 

I want to have outcome as below picture with 5 columns only. 

I used unpivot function to bring courses in one column which worked but I have to done it thrice because I have to make Cousrses, Y/N and Compliance columns to do that I have to unpivot the thre course columns thrice which are increasing the number of rows to alot and results are not producing correctly. How to get it done correctly without doing it thrice and keeping the data in limit without changing getting lots of wromg data. Kindly help.

Thanks

[CNENFRNL · Accepted solution]

No need to pivot at all,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBC4MwDIX/S89CkorTnnfbwcNuQzwIKyhM61Z38N8vaS2IY7BDyksgX15f06hLN1qvMqU1YA4aNUoDxJJEUgGEQMYYbm5cNdfKdXbj/Bi6aZGZk/dqn+/hZe+qzZjr+knWK8DAknUGlaJL1jkBaWmKHbP+Zu0PBW7nXQCX0S8KjKLfII345eaUeIl95MogOvV27nlQAVFKIFHyoI0cqrYA1p8BHJxa7wcbv10IwER7aFIeegsnXPw3g/YD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Student = _t, Course1 = _t, Course2 = _t, Course3 = _t, Course1.1 = _t, Course2.1 = _t, Course3.1 = _t, Course1.2 = _t, Course2.2 = _t, Course3.2 = _t]),

    Count = 3,
    Courses = List.FirstN(List.Skip(Table.ColumnNames(Source)),Count),
    Tables = let Cols = Table.ToColumns(Source), st = Cols{0}, cols = List.Zip(List.Split(List.Skip(Cols),Count)) in List.Transform(cols, each Table.FromColumns({st} & _, {"Student","Date","Y/N","Compliance"})),
    #"Added Course" = Table.Combine(List.Transform({0..Count-1}, each Table.AddColumn(Tables{_}, "Course", (r) => Courses{_})))
in
    #"Added Course"

[Ashish_Mathur]

Hi,

Why should the headings in your data repeat?  B1 should ideally be something like Course1 - Date, E1 should be Coure1 - Response and H1 should be Course1 - Compliance.  This should be for the other headings as well.  If you can do this, then there is a very simple way (using the Query Editor) to generate your required result.

[mahoneypat]

This could be done in a few steps with a custom column that creates a list of records.  Please see this video.  

Faster Data Transformations with List/Record M Functions - YouTube

I would able to extract your data (mostly) from the image (better to share it in a copy/paste-able format next time). Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.  The key step is in the Added Custom step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBC4MwDIX/S89CkorTnnfbwcNuQzwIKyhM61Z38N8vaS2IY7BDyksgX15f06hLN1qvMqU1YA4aNUoDxJJEUgGEQMYYbm5cNdfKdXbj/Bi6aZGZk/dqn+/hZe+qzZjr+knWK8DAknUGlaJL1jkBaWmKHbP+Zu0PBW7nXQCX0S8KjKLfII345eaUeIl95MogOvV27nlQAVFKIFHyoI0cqrYA1p8BHJxa7wcbv10IwER7aFIeegsnXPw3g/YD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Student = _t, Course1 = _t, Course2 = _t, Course3 = _t, Course1.1 = _t, Course2.1 = _t, Course3.1 = _t, Course1.2 = _t, Course2.2 = _t, Course3.2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Course1", type text}, {"Course2", type text}, {"Course3", type text}, {"Course1.1", type text}, {"Course2.1", type text}, {"Course3.1", type text}, {"Course1.2", type text}, {"Course2.2", type text}, {"Course3.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {[Course = "Course1", Date = [Course1], YN = [Course1.1], Compliance = [Course1.2]], [Course = "Course2", Date = [Course2], YN = [Course2.1], Compliance = [Course2.2]], [Course = "Course3", Date = [Course3], YN = [Course3.1], Compliance = [Course3.2]]}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Student", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"Course", "Date", "YN", "Compliance"}, {"Course", "Date", "YN", "Compliance"})
in
    #"Expanded Custom1"

Pat