locked
Pivot Function in Power Query RRS feed

  • Question

  • Hi all,

    One thing i'm struggling with is the use of the pivot function to pivot a column within PQ.  I'm confused on why this happens and why I can't clear it up...but here goes:

    • I unpivot a set of columns which then gives me two columns (Attribute/Value).  I do this because I have repeated columns of data, but it should all fall under one column (think Hours, Hours1, Hours2, etc) - basically the input is coming from a form that has repeating fields but everything is the same data type.
    • I make some adjustments to the Attribute columns (Replace items within the Attribute column) then Pivot the two columns again...theoretically I would be able to do this since i just unpivoted the same data, but:
    • I get tons of errors (usually "There were too many elements in the enumeration to complete the operation".).  I'm not entirely sure why this happens and it doesn't seem to make sense to me that I can't UnPivot and Pivot and get the same results as I originally had.  

    Any recommendations would be greatly appreciated!

    EDIT: I'm choosing "don't aggregate" during the Pivot operation...I get values if I keep some sort of aggregate function, but not the right values (I get a count of the fields instead of the data within the fields)
    • Edited by Jimmy Haley Wednesday, March 18, 2015 2:08 PM Added more details
    Wednesday, March 18, 2015 2:06 PM

Answers

  • Thanks, guys.  Late last night (before I saw this solution), I actually was able to build the query to work perfectly using a little different method.  In essence, I defined each set of columns that I wanted via a Table.SelectRows command, renamed them to the correct name, then appended them all together with a Table.Combine command.  This solution appears to work smoothly and the query is pretty fast.  

    It was a little bit of a workaround and doesn't involve pivoting/unpivoting, but the result is the same thing. 

    • Proposed as answer by Michael Amadi Friday, March 20, 2015 9:41 AM
    • Marked as answer by Michael Amadi Friday, March 27, 2015 10:33 AM
    Thursday, March 19, 2015 3:27 PM

All replies

  • Hi Jimmy,

    obvious first: You're pivoting the Attribute column, not the Value?

    Could it be that your adjustments to the attribute columns produced too many different attribute values (check this by loading the table to the workbook before pivoting back). How many rows does it produce?


    Imke

    Wednesday, March 18, 2015 2:55 PM
  • Thanks for the quick reply.  For your questions:

    Yes, I'm pivoting the Attribute column - the column headers end up correct (in my example, I end up with Hours as the column), but errors throughout.  And just to check myself, I've reversed it and I end up with the values as the headers of each column.

    Regarding the adjustments, the odd thing is that the adjustments are actually REDUCING the amount of attribute values.  For instance, after unpivoting the columns, the attributes may contain HOURS, HOURS2, HOURS3, HOURS4...my adjustments are simply removing the numbers, so I only end up with HOURS as the only attribute...on the re-pivot operation, I end up with an Hours column, but errors in the values below it.

    Wednesday, March 18, 2015 3:11 PM
  • I'm trying to upload an image to show the steps I'm attempting, but I get an error that I am not allowed to do that until my account is "verified"...which I have no idea on how to do either, since I'm pretty confident I'm verified...if I can figure that out, I'll post a screenshot.

    Wednesday, March 18, 2015 3:29 PM
  • Ok, here's a screenshot of the steps I'm trying to accomplish:

    Wednesday, March 18, 2015 3:54 PM
  • You cannot pivot a column if the column values are not unique...unless you use an aggregation function.
    Wednesday, March 18, 2015 4:00 PM
  • Try this solution:
    • Once you are to step 3, add an index column that starts at 0 and another index column that starts at 1. 
    • Transform by Pivot Column using the names in Attribute to create new columns and the Values Column set to Value.  Under advanced options select Don't Aggregate. 
    • Fill Down the Date column
    • Filter rows to remove null from Work Time. 

    Here's the M code from the advanced editor: 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
        #"Pivoted Column" = Table.Pivot(#"Added Index1", List.Distinct(#"Added Index1"[Attribute]), "Attribute", "Value"),
        #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Date"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Work Time] <> null)),
        #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Work Time", type time}})
    in
        #"Changed Type"

    If that doesn't work, this example might do the trick:

    http://www.excelguru.ca/blog/2015/01/07/transpose-stacked-tables/

    Thursday, March 19, 2015 1:50 AM
  • I tried Jiminie's ingenious solution, and it required a touch of tweaking as I understand the problem

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
        #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns"," 1","",Replacer.ReplaceText,{"Attribute"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," 2","",Replacer.ReplaceText,{"Attribute"}),
        // any more replacements here
        #"Added Index" = Table.AddIndexColumn(#"Replaced Value1", "Index", 0, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
        #"Pivoted Column" = Table.Pivot(#"Added Index1", List.Distinct(#"Added Index1"[Attribute]), "Attribute", "Value"),
        #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Work Time"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Over] <> null)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Index.1"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}})
    in 
        #"Changed Type"


    Thursday, March 19, 2015 1:07 PM
  • Thanks, guys.  Late last night (before I saw this solution), I actually was able to build the query to work perfectly using a little different method.  In essence, I defined each set of columns that I wanted via a Table.SelectRows command, renamed them to the correct name, then appended them all together with a Table.Combine command.  This solution appears to work smoothly and the query is pretty fast.  

    It was a little bit of a workaround and doesn't involve pivoting/unpivoting, but the result is the same thing. 

    • Proposed as answer by Michael Amadi Friday, March 20, 2015 9:41 AM
    • Marked as answer by Michael Amadi Friday, March 27, 2015 10:33 AM
    Thursday, March 19, 2015 3:27 PM
  • Can you post the M code for that solution, for the archives?
    Thursday, March 19, 2015 4:19 PM