Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
5 months ago
Solved

Table Pivot, result explanation

Hi,  if i have a table ;  = #table( type table [ Name = text , Item = text ], { {"Jon", "a"} , {"Jon", "a"} , {"Jon", "b"}, { "Jon", "b"}, {"alan", "a"} ,{"alan", "a"}} ) and I want the 'item...
Need Help
Show and Tell
  • pcoley's avatar
    pcoley
    5 months ago

    Dicken 
    Given a pair of columns representing attribute-value pairs, Table.pivot rotates the data in the attribute column into column headings.

    The syntax of the Table.pivot is: 

     
    Table.Pivot(
   table as table,
   pivotValues as list,
   attributeColumn as text,
   valueColumn as text,
   optional aggregationFunction as nullable function,
) as table

    As you want the item distinct values to be the headers your formula must be: 
    = Table.Pivot(
  Source, 
  List.Distinct(Source[Item]), 
  "Item",  
  "Name", 
  each Text.Combine(_,",") 
)​

    I hope this helps. if so please mark it as a solution. Kudos are welcome!

Natarajan_M's avatar
Natarajan_M
Super User
5 months ago

Hi Dicken , 

You are correct; in your first approach, you are missing the values, which is why the attributes are being concatenated instead of the values. In your second approach, you are assigning the values to the attributes, which is why it works as expected.

Please refer to the documentation for more information.
https://learn.microsoft.com/en-us/powerquery-m/table-pivot
Thanks

If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster.




https://learn.microsoft.com/en-us/powerquery-m/table-pivot