March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.