Pivot problem

[Anonymous]

I have my table to the left and I need the code to get the table to the right. I tried this but it doesnt work in my case, atleast not to my knowledge.

Help is greatly appreciated. thanks!

[Anonymous · Accepted solution]

Hi @Anonymous  & @Jimmy801 ,

An alternative version using less grouping and, also, not using hardcoded column names (which may be a pain when you switch to actual data/table):

let
	Source = #table 
	(
		{"Col1","Col2","Col3","Col4"},
		{ {"x1","y1","1","attribute1"}, {"x1","y1","11","attribute2"}, {"x1","y1","111","attribute3"}, {"x1","y1","2","attribute1"}, {"x1","y1","22","attribute2"}, {"x1","y1","222","attribute3"}, {"x2","y2","3","attribute1"}, {"x2","y2","33","attribute2"}, {"x2","y2","333","attribute3"}, {"x2","y2","4","attribute1"}, {"x2","y2","44","attribute2"}, {"x2","y2","444","attribute3"} } 
	),
    
	PivotFunction = (pTable as table, pAttributesFieldName as text, pValuesFieldName ) => 
							Table.FromRecords(
								List.Accumulate(Table.ToRecords(pTable), {[]}, (s,a) => 
									let 
										GetRecord = List.Last(s),
										AttributeFieldName = Record.Field(a, pAttributesFieldName),
										ValueFieldName = Record.Field(a, pValuesFieldName),
										UpdateRecord  = if Record.HasFields(GetRecord, a[Col4]) 
														then s & {Record.AddField([], AttributeFieldName, ValueFieldName)} 
														else List.RemoveLastN(s,1) & {Record.AddField(GetRecord, AttributeFieldName, ValueFieldName)} 
									in UpdateRecord)
							),
	
	#"Grouped Rows" = Table.Group(Source, {"Col1", "Col2"}, {{"Data", each PivotFunction(_, "Col4", "Col3")}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", List.Distinct(Source[Col4]), List.Distinct(Source[Col4]))
in
    #"Expanded Data"

Most of the action happening inside the PivotFunction, which tales (attributes column and value column names, in this case, Col4 and Col3 as additional arguments - this is the only bit that needs to be changed to match your actual table schema) builds a pivoted version of the data foes each grouping. Please also note how List.Distinct is used on the last step to automate the data expansion.

Kind regards,

JB

[Jimmy801]

Hello @Anonymous 

check out this solution. Uses Table.Group to group per attribute always starting with attribute1, adding an index on every table that was grouped and pivoting it afterwards

let
	Source = #table 
	(
		{"Col1","Col2","Col3","Col4"},
		{ {"x1","y1","1","attribute1"}, {"x1","y1","11","attribute2"}, {"x1","y1","111","attribute3"}, {"x1","y1","2","attribute1"}, {"x1","y1","22","attribute2"}, {"x1","y1","222","attribute3"}, {"x2","y2","3","attribute1"}, {"x2","y2","33","attribute2"}, {"x2","y2","333","attribute3"}, {"x2","y2","4","attribute1"}, {"x2","y2","44","attribute2"}, {"x2","y2","444","attribute3"} } 
	),
	ChangeType = Table.TransformColumnTypes
	(
		Source,
		{{"Col3", Int64.Type}}
	),
	Group = Table.Group
	(
		ChangeType,
		{"Col4"},
		{{"AllRows", each _}},
		GroupKind.Local,
		(
			curr,
			group
		)=>
		if Value.Compare
		(
			group[Col4],
			"attribute1"
		)
	<>0 then 0 else 1
),
AddIndex = Table.AddIndexColumn
(
	Group,
	"Index",
	1,
	1
),
AddNewColumnIndex = Table.AddColumn
(
	AddIndex,
	"AllRowsNew",
	each Table.AddColumn
	(
		[AllRows],
		"Index",
		(
			new
		)=>
		[Index]
	)
),
DeleteOther = Table.SelectColumns
(
	AddNewColumnIndex,
	{"AllRowsNew"}
),
ExpandTable = Table.ExpandTableColumn
(
	DeleteOther,
	"AllRowsNew",
	{"Col1", "Col2", "Col3", "Col4", "Index"},
	{"Col1", "Col2", "Col3", "Col4", "Index"}
),
Pivot = Table.Pivot
(
	ExpandTable,
	List.Distinct
	(
		ExpandTable[Col4]
	),
	"Col4",
	"Col3",
	List.Sum
),
DeleteIndex = Table.RemoveColumns
(
	Pivot,
	{"Index"}
)
 in 
	DeleteIndex

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous · Accepted solution]

Hi @Anonymous  & @Jimmy801 ,

An alternative version using less grouping and, also, not using hardcoded column names (which may be a pain when you switch to actual data/table):

let
	Source = #table 
	(
		{"Col1","Col2","Col3","Col4"},
		{ {"x1","y1","1","attribute1"}, {"x1","y1","11","attribute2"}, {"x1","y1","111","attribute3"}, {"x1","y1","2","attribute1"}, {"x1","y1","22","attribute2"}, {"x1","y1","222","attribute3"}, {"x2","y2","3","attribute1"}, {"x2","y2","33","attribute2"}, {"x2","y2","333","attribute3"}, {"x2","y2","4","attribute1"}, {"x2","y2","44","attribute2"}, {"x2","y2","444","attribute3"} } 
	),
    
	PivotFunction = (pTable as table, pAttributesFieldName as text, pValuesFieldName ) => 
							Table.FromRecords(
								List.Accumulate(Table.ToRecords(pTable), {[]}, (s,a) => 
									let 
										GetRecord = List.Last(s),
										AttributeFieldName = Record.Field(a, pAttributesFieldName),
										ValueFieldName = Record.Field(a, pValuesFieldName),
										UpdateRecord  = if Record.HasFields(GetRecord, a[Col4]) 
														then s & {Record.AddField([], AttributeFieldName, ValueFieldName)} 
														else List.RemoveLastN(s,1) & {Record.AddField(GetRecord, AttributeFieldName, ValueFieldName)} 
									in UpdateRecord)
							),
	
	#"Grouped Rows" = Table.Group(Source, {"Col1", "Col2"}, {{"Data", each PivotFunction(_, "Col4", "Col3")}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", List.Distinct(Source[Col4]), List.Distinct(Source[Col4]))
in
    #"Expanded Data"

Most of the action happening inside the PivotFunction, which tales (attributes column and value column names, in this case, Col4 and Col3 as additional arguments - this is the only bit that needs to be changed to match your actual table schema) builds a pivoted version of the data foes each grouping. Please also note how List.Distinct is used on the last step to automate the data expansion.

Kind regards,

JB

[Anonymous]

Thank you both, I did not imagine this problem to be this demanding.

@Anonymous Would you perhaps be so kind as to show me how to embed this into a function? 

input would/could be like 

jborrosFunction(MyTable as table, colnames_group as list, colnames_pivot as list) as table =>

<your code here, taken the above input variables>

where in this case, colnames_group = {"col1", "col2"} and colnames_pivot= {"col3", "col4"}

I marked your solution as correct, and would greatly appreciate if you could go the last mile. If I can award you in any way on this site, I will 🙂

[Anonymous · Accepted solution]

Hi @henrykloev,

Thank you for your charming smile.
I am away from my laptop, so not able to test, but hopefully the code below will work. If not, I will test and repost in 12 hrs. Fingers crossed...

fPivotEx(myTable as table, colname_pivot_attribute as text, colname_pivot_value as text, optional colnames_group as list) as table =>

let
//setting up some variables
Source = myTable,
mGroupByColumns = if colnames_group = null then List.RemoveItems(Table.ColumnNames(Source), {colname_pivot_attribute, colname_pivot_value}) else colnames_group, // sets group by to other columns if ommitted in the function call for simplicity
mPivotedColumnNames = List.Buffer(List.Distinct(Table.Column(Source, colname_pivot_attribute)),

// pivot function, note that I've removed a hard coded reference to Col4 from this function overlooked in my original post.
PivotFunction = (pTable as table, pAttributesFieldName as text, pValuesFieldName ) =>
Table.FromRecords(
List.Accumulate(Table.ToRecords(pTable), {[]}, (s,a) =>
let
GetRecord = List.Last(s),
AttributeFieldName = Record.Field(a, pAttributesFieldName),
ValueFieldName = Record.Field(a, pValuesFieldName),
UpdateRecord = if Record.HasFields(GetRecord, AttributeFieldName)
then s & {Record.AddField([], AttributeFieldName, ValueFieldName)}
else List.RemoveLastN(s,1) & {Record.AddField(GetRecord, AttributeFieldName, ValueFieldName)}
in UpdateRecord)),

// main body
#"Grouped Rows" = Table.Group(Source, mGroupByColumns {{"Data", each PivotFunction(_, colname_pivot_attribute, colname_pivot_value)}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", mPivotedColumnNames, mPivotedColumnNames)
in
#"Expanded Data"