cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dat_Data Frequent Visitor
Frequent Visitor

Pivot data in powerquery

Hi all, 

 

I got an excel sheet that looks like this and want to make it look like the one below using PowerQuery... I've tried the unpivot function but it behavious in a funny way. Help is appreciated. 

 

CategoryEventDateEventDateEventDateEventDateEventDateEventDateEventDateEventDateEventDate
AAAA25/5/2018B27/5/2018C4/6/2018D21/7/2018R22/8/2018F20/12/2018G18/2/2019H10/3/2019I19/4/2019
BBBJ16/6/2018K18/6/2018L26/6/2018M12/8/2018N13/9/2018O11/1/2019P12/3/2019Q1/4/2019S11/5/2019

 

CategoryEventDate
AAAA25/5/2018
AAAB27/5/2018
AAAC4/6/2018
AAAD21/7/2018
AAAR22/8/2018
AAAF20/12/2018
AAAG18/2/2019
AAAH10/3/2019
AAAI19/4/2019
BBBJ16/6/2018
BBBK18/6/2018
BBBL26/6/2018
BBBM12/8/2018
BBBN13/9/2018
BBBO11/1/2019
BBBP12/3/2019
BBBQ1/4/2019
BBBS11/5/2019

 

Thanks,
Dat_Data

1 ACCEPTED SOLUTION

Accepted Solutions
Stachu Super Contributor
Super Contributor

Re: Pivot data in powerquery

Unpivot would do the job if the Event names were in the column headers. With this data structure there will be no quick and easy solution I'm afraid

 

If the data is coming from an Excel pivot, why not just rearrange it in Excel?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

4 REPLIES 4
Stachu Super Contributor
Super Contributor

Re: Pivot data in powerquery

Unpivot would do the job if the Event names were in the column headers. With this data structure there will be no quick and easy solution I'm afraid

 

If the data is coming from an Excel pivot, why not just rearrange it in Excel?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Dat_Data Frequent Visitor
Frequent Visitor

Re: Pivot data in powerquery

I accept your post as a solution since i agree there isnt an easy way to make this work...however, one thing that just might work (but is not a good time investment) is to chop the table into several ones via DAX and then append the tables together...you're right, ill just do it through excel Smiley Happy 

Thanks, 

Dat_Data

Stachu Super Contributor
Super Contributor

Re: Pivot data in powerquery

oh absolutely, you can also do it with PowerQuery/M, not only DAX, but I honestly don't think it's worth the time

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Pivot data in powerquery

@Dat_Data @Stachu 

 

Pardon my intrusion. There is a little trick which might work (I think I learnt from one of @parry2k 's solutions which used Number.Mod)
I am attaching the Excel file. See if this useful.
It works with the sample data and mostly done using Query Editor's interface

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, 
{"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}, {"Column12", type text}, {"Column13", type any}, {"Column14", type text}, {"Column15", type any}, {"Column16", type text}, {"Column17", type any}, {"Column18", type text}, {"Column19", type any}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Column1] <> "Category")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]-Number.Mod([Index],Table.RowCount(Source)-1)), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom", "Column1", "Value"}), #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom", "Column1"}, {{"All", each _[Value]}}), #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns2", "Custom", each Text.Combine(List.Transform([All],each Text.From(_)),"|")), #"Removed Columns3" = Table.RemoveColumns(#"Added Custom1",{"All"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns3", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type datetime}}) in #"Changed Type1"

 

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 133 members 1,767 guests
Please welcome our newest community members: