Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    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!

4 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        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"