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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

View solution in original post

4 REPLIES 4
Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

Anonymous
Not applicable

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 🙂 

Thanks, 

Dat_Data

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

 

 


Regards
Zubair

Please try my custom visuals
Stachu
Community Champion
Community Champion

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!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors