Table Pivot, result explanation

[Dicken]

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 'items' as headers and then a concat of the values below, so grouped by name, i thought i could use; 

= Table.Pivot( Source, {"a","b"}, 
"Item", "Item", each Text.Combine(_,",") )

but this puts teh 'name' in the values ?   can some explaine why this happens,   I have found that if i dupilicate the item 
column then i can get the result i was looking for  ; 

= Table.Pivot( 
Table.AddColumn(  Source, "V",
each [Item] ), List.Distinct( Source [Item] ), 
"Item", "V" , each Text.Combine(_,",") )

so the problem seems to be caused when 'attribute and value are the same,  just wonderd if anyone had any insights as to 
why this happens ? 

Richard. 

[pcoley · Accepted solution]

@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]

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

[NattyM]

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