Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Pivot removes valueColumn

Hello

 

When I use the Table.Pivot method, the valueColumn is not contained in the output. How can I retain it without having to duplicate it first?

 

So, imagine my raw data is:

 

Name, Attribute

John, Red

John, Green

Sally, Red

 

If I use:

 

= Table.Pivot(#"etc", List.Distinct(#"etc"[Attribute]), "Attribute", "Name", List.Count)

 

 

I would expect to see:

Name, Red, Green

John, 1, 1

Sally, 1, 0

 

Instead I get:

Red, Green

1, 1

1, 0

 

I can't imagine why that would ever be useful. To work around it, I am duplicating the Name column first. Is there a way of achieving the same thing without having to duplicate the column first?

Need Help
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    Hi Anonymous 

     

    Try this instead:

     

    = Table.Pivot(#"etc", List.Distinct(#"etc"[Attribute]), "Attribute", "Attribute", List.Count)

     

    The "valueColumn" specified in the 4th argument of Table.Pivot is always removed from the resulting table.

    So if you use "Attribute" there (which is removed anyway as it is also the "attributeColumn"), then as long as counting values in the Attribute column gives the correct result, this should give the correct pivoted table.

     

    Regards,

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    3 years ago

    Hi Anonymous 

     

    Try this instead:

     

    = Table.Pivot(#"etc", List.Distinct(#"etc"[Attribute]), "Attribute", "Attribute", List.Count)

     

    The "valueColumn" specified in the 4th argument of Table.Pivot is always removed from the resulting table.

    So if you use "Attribute" there (which is removed anyway as it is also the "attributeColumn"), then as long as counting values in the Attribute column gives the correct result, this should give the correct pivoted table.

     

    Regards,