Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
@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"
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
17 | |
11 | |
9 | |
8 |
User | Count |
---|---|
42 | |
24 | |
21 | |
13 | |
11 |