Forum Discussion

pickslides's avatar
pickslides
Helper I
3 years ago

Data Transformation - Power Query

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

 

NameSubjectDateSession
JohnMath14/07/20231
JohnEnglish11/07/20232
JohnDrama11/07/20231
MaryMath15/07/20231
MaryEnglish15/07/20232
MaryDrama13/07/20231
MaryScience13/07/20233

.

this is after 

 

NameSubject1Date1Session1Subject2Date2Session2Subject3Date3Session3Subject4Date4Session4
JohnMath14/07/20231English11/07/20232Drama11/07/20231   
MaryMath15/07/20231English15/07/20232Drama13/07/20231Science13/07/20233

 

 


Thanks

Q

Need Help

1 Reply

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    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"