Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
6 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
    6 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!

pcoley's avatar
pcoley
Super User
6 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!

  • Dicken's avatar
    Dicken
    Post Prodigy
    6 months ago

    Thanks, that sort of shows what's happenin, stil strange that it does it.