Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pickslides
Helper I
Helper I

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

1 REPLY 1
Mahesh0016
Super User
Super User

@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"

Mahesh0016_0-1689688615350.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors