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. 

[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