Forum Discussion
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 '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.
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!
3 Replies
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.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!
Thanks, that sort of shows what's happenin, stil strange that it does it.
