How to rearreange a table based on a set of columns?

[pie333]

Hi I am trying to rearrange a table so that the value in one column is assigned to a set of columns.

As an example take this table:

farmer	total land (ac)	cultivated surface	plot A cultivated plant	plot A cultivated surface (ac)	plot A workers	plot A yield in kg	plot A expenses	plot A selling price per kg	plot A yield expected value	plot B cultivated plant	plot B cultivated surface (ac)	plot B workers	plot B yield in kg	plot B expenses	plot B selling price per kg	plot B yield expected value	plot C cultivated plant	plot C cultivated surface (ac)	plot C workers	plot C yield in kg	plot C expenses	plot C selling price per kg	plot C yield expected value
George	12	6	Coffee	2	2	120	1 200 €	15 €	1 800 €	Banana	3	1	300	700 €	3 €	900 €	Tea	1	2	60	600 €	25.0 €	1 500 €
Mark	8	5.5	Orange	1	0	50	100 €	1.50 €	75 €	Apple	2	2	750	400 €	1.5	1 125 €	Coffee	2.5	2	100	160 €	10.0 €	1 000 €
Anna	23	22	Banana	22	3	1230	1 800 €	8	9 840 €	0	0	0	0	-   €	0	0 €	0	0	0	0	-   €	-   €	0 €
Yuri	6	5.5	Avocado	3	1	400	2 000 €	6	2 400 €	Strawberry	2.5	4	90	500 €	10	900 €	0	0	0	0	-   €	-   €	0 €

Is it possible to transform it with Power Query to this table? How?

farmer	total land (ac)	cultivated surface	Coffee cultivated surface (ac)	coffe workers	coffee yield in kg	coffee expenses	coffee selling price per kg	coffee yield expected value	Orange cultivated surface (ac)	Orange workers...
George	12	6	2	2	120	1 200 €	15 €	1 800 €		...
Mark	8	5.5	2.5	2	100	160 €	10.0 €	1 000 €	1	...
Anna	23	22								...
Yuri	6	5.5								...

I am sorry I had to crop the "new table" because the post exceeded the limit of 20 000 characters: it would have had each "plant" with its values (workers, yield, expenses and so on) as single columns, filled only on the specific farmers who had those values in the first table (George and Mark have Coffee, Mark would have had also oranges etc... )

Thank you!

[Jakinta · Accepted solution]

You can try this in blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVFNC8IwDP0rY2eVLq5uHqcHT+JBLzI8VO1ElE2KH/jvbV6KVhAZSZuXLH0vqet0Zjt3sGkvzci7kbdp1zSWEQqWkWKfkMKpEZQIJqb1n78MGeQTcAHP2Bi3lTUhj0cUHHvSA+mtfbjp1encuJMHSm96wC8tnGmFnzeu1fhBqKCiYFddLueYc4GyPCrLkoz0lzzA9Ok2ks6BkQqMqhYCidUQxZoRQTgNVTQUJj9OylwFxrH1f2BxTvL87vrmjmEjMonq3u3MvouGLfIIVKWSEsGWV2ceW+vc8y00xzYwQNH53s4fHpsX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [farmer = _t, #"total land (ac)" = _t, #"cultivated surface" = _t, #"plot A cultivated plant" = _t, #"plot A cultivated surface (ac)" = _t, #"plot A workers" = _t, #"plot A yield in kg" = _t, #"plot A expenses " = _t, #"plot A selling price per kg" = _t, #"plot A yield expected value" = _t, #"plot B cultivated plant" = _t, #"plot B cultivated surface (ac)" = _t, #"plot B workers" = _t, #"plot B yield in kg" = _t, #"plot B expenses " = _t, #"plot B selling price per kg" = _t, #"plot B yield expected value" = _t, #"plot C cultivated plant" = _t, #"plot C cultivated surface (ac)" = _t, #"plot C workers" = _t, #"plot C yield in kg" = _t, #"plot C expenses " = _t, #"plot C selling price per kg" = _t, #"plot C yield expected value" = _t]),
    Cols = Table.ColumnNames(Source),
    PlotA = Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source, List.Combine({List.FirstN(Cols,3),List.Split(List.Skip(Cols,3),7){0}}))),1),
    PlotB = Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source, List.Combine({List.FirstN(Cols,3),List.Split(List.Skip(Cols,3),7){1}}))),1),
    PlotC = Table.Skip(Table.DemoteHeaders(Table.SelectColumns(Source, List.Combine({List.FirstN(Cols,3),List.Split(List.Skip(Cols,3),7){2}}))),1),
    Appended = Table.Combine({PlotA, PlotB, PlotC}),
    Names = Table.ReplaceValue(Table.FromColumns({List.FirstN(Cols,3)&List.FirstN(List.Skip(Cols,4),6)}),"plot A","##",Replacer.ReplaceText,{"Column1"}),
    Grouped = Table.Group(Appended, {"Column4"}, {{"Gr", each Table.SelectColumns(_,List.RemoveItems(Table.ColumnNames(_), {"Column4"})), type table }}),
    Filtered = Table.SelectRows(Grouped, each ([Column4] <> "0")),
    Renamed = Table.AddColumn(Filtered, "Custom", each Table.RenameColumns( [Gr], 
List.Zip( {  Table.ColumnNames([Gr]) ,
 Table.ReplaceValue(Names,"##",[Column4],Replacer.ReplaceText,{"Column1"})[Column1]} ))),
    Removed = Table.RemoveColumns(Renamed,{"Gr", "Column4"}),
    Expanded = Table.ExpandTableColumn(Removed, "Custom",  List.Union(List.Transform(Removed[Custom], each Table.ColumnNames(_)))  ),
    Grouped2 = Table.Group(Expanded, {"farmer", "total land (ac)", "cultivated surface"}, {{"All", each Table.FirstN(Table.FillUp(Table.SelectColumns(_,List.Skip(Table.ColumnNames(_),3)),List.Skip(Table.ColumnNames(_),3)), 1), type table }}),
    Expanded2 = Table.ExpandTableColumn(Grouped2, "All", List.Union(List.Transform(Grouped2[All], each Table.ColumnNames(_)))  ),
    FINAL = Table.ReplaceValue(Expanded2,null,"",Replacer.ReplaceValue,Table.ColumnNames(Expanded2))
in
    FINAL