Data Transformation - Power Query

[pickslides]

Hi there,

I want to pivot / unpivot some data maybe using power query or something else. Happy to take suggestions.

I have a data set I want to transform for a mail merge, so the idea is the get all data into the one record. Normal unpivot columns in Power Query doesn't give the desired result.

This is before

Name	Subject	Date	Session
John	Math	14/07/2023	1
John	English	11/07/2023	2
John	Drama	11/07/2023	1
Mary	Math	15/07/2023	1
Mary	English	15/07/2023	2
Mary	Drama	13/07/2023	1
Mary	Science	13/07/2023	3

.

this is after 

Name

Subject1

Date1

Session1

Subject2

Date2

Session2

Subject3

Date3

Session3

Subject4

Date4

Session4

John

Math

14/07/2023

1

English

11/07/2023

2

Drama

11/07/2023

1

Mary

Math

15/07/2023

1

English

15/07/2023

2

Drama

13/07/2023

1

Science

13/07/2023

3

Thanks

Q

[Mahesh0016]

@pickslides Please Use below M Code.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUfJNLMkAUoYm+gbm+kYGRsYgjlKsDlyBa156TmYxWI0hkhojZDUuRYm5iegqIKb4JhZVIlljiksBkjWmGNZA1cCtMcZlSnByZmpeciq6GmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Subject = _t, Date = _t, Session = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Subject", type text}, {"Date", type text}, {"Session",type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Columns", each _[Subject]},{"Date", each Text.Combine(_[Date],"|")},{"Session", each Text.Combine(_[Session],"|")}}),
#"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Columns", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Split Column by Character Transition" = Table.SplitColumn(#"Extracted Values", "Columns", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Columns.1", "Columns.2", "Columns.3", "Columns.4"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Character Transition", "Date", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), {"Date.1", "Date.2", "Date.3", "Date.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Columns.1", type text}, {"Columns.2", type text}, {"Columns.3", type text}, {"Columns.4", type text}, {"Date.1", type date}, {"Date.2", type date}, {"Date.3", type date}, {"Date.4", type date}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Session", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), {"Session.1", "Session.2", "Session.3", "Session.4"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Session.1", Int64.Type}, {"Session.2", Int64.Type}, {"Session.3", Int64.Type}, {"Session.4", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Session.1", "Session1"}, {"Session.2", "Session2"}, {"Session.3", "Session3"}, {"Session.4", "Session4"}, {"Columns.1", "Subject1"}, {"Columns.2", "Subject2"}, {"Columns.3", "Subject3"}, {"Columns.4", "Subject4"}, {"Date.1", "Date1"}, {"Date.2", "Date2"}, {"Date.3", "Date3"}, {"Date.4", "Date4"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Name", "Subject1", "Date1", "Session1", "Subject2", "Date2", "Session2", "Subject3", "Date3", "Session3", "Subject4", "Date4", "Session4"})
in
#"Reordered Columns"