Transform list data into formatted matrix based on values using Power Query

Question

Background

I recently took a position at an orchard. The planting maps and other data are stored in many different files, each with different formatting and structures, maintained and created by different individuals. When a change is made in the orchard (i.e. a tree is planted, or dies) information has to be updated on several different files – which is cumbersome and extremely prone to error.

A goal of mine is to store all the planting data in one file (which only I make edits to), from which other summary files and reports can be generated automatically and distributed. This question pertains to one style of summary file that many people at the orchard use. It is a matrix that has some spatial correlation to the layout of our orchard (e.g. the rows and columns of the worksheet correspond to cardinal directions and mimic the layout of the orchard)

Question

Several supervisors at the orchard prefer to view the planting data in matrix form, where the direction of the columns and rows relate to the cardinal direction (N, E, S, W) and the layout of the orchard (see example).

How can I take this data in list form, and transform it into a matrix with specific formatting?

I have tried different combinations of Group By, Transpose, Unpivot Columns, Split Column, Pivot Column with no success.

Requirements

I want to have one “master” worksheet that is maintained that I make edits to. There will be several other worksheets or workbooks that automatically update from my master worksheet.

1. Each value for BlockN will have a dedicated worksheet (Note: In this example, there is only one value for BlockN, in the actual dataset there are currently 13 distinct values).
2. Each distinct value for RowN will have a dedicated column in the output.
   • The order of the distinct values in RowN will be reversed (i.e. sorted left --> right from Z --> A).
   • The heading for each column will be a concatenation of the unique values of RowN and RowNm.
3. Each distinct value in the Loc column will have a dedicated row.
4. The value in each of the output cells with be a concatenation of Variety and Planting Year.
5. There will be conditional formatting to each cell dependent on the corresponding value for Status.
   • Living = Green fill
   • Dead = Red fill
   • Unplanted = Blue fill

Preferred, but not necessarily required.

1. Formatting within the output
   • Cardinal directions (North, East, South, West) around the borders of the output.
   • A title that is a concatenation of the distinct values for Block and BlockNm.

Potential problems?

1. How will this handle blanks? There are going to be some cells that are blank because nothing was planted there
2. What if there are different ranges of values for loc. For example RowN A might be “001 – 060” and RowN B might be “001 – 120”

Example Source Data

Variety	Block	BlockNm	RowN	RowNm	Loc	Planting Year	Status	RowSp	LocSp
Green	1	Fruit Tree	A	Short Trees	001	2022	Living	12	3
Red	1	Fruit Tree	A	Short Trees	002	2020	Living	12	3
Tasty	1	Fruit Tree	A	Short Trees	003	2022	Dead	12	3
Blue	1	Fruit Tree	A	Short Trees	004	2019	Unplanted	12	3
Sour	1	Fruit Tree	A	Short Trees	005	2020	Living	12	3
Sunrise	1	Fruit Tree	B	Vines	001	2022	Living	12	3
Sunrise	1	Fruit Tree	B	Vines	002	2019	Dead	12	3
Sunrise	1	Fruit Tree	B	Vines	003	2021	Living	12	3
Beauty	1	Fruit Tree	B	Vines	004	2020	Dead	12	3
Beauty	1	Fruit Tree	B	Vines	005	2020	Living	12	3
Green.5	1	Fruit Tree	C	Tall Trees	001	2019	Living	12	3
Green.4	1	Fruit Tree	C	Tall Trees	002	2019	Unplanted	12	3
Green.3	1	Fruit Tree	C	Tall Trees	003	2022	Unplanted	12	3
Green.2	1	Fruit Tree	C	Tall Trees	004	2021	Living	12	3
Green.1	1	Fruit Tree	C	Tall Trees	005	2022	Unplanted	12	3
Big Leaf	1	Fruit Tree	D	Shrubs	001	2019	Unplanted	12	3
Big leaf	1	Fruit Tree	D	Shrubs	002	2019	Unplanted	12	3
Yellow Trumpit	1	Fruit Tree	D	Shrubs	003	2022	Dead	12	3
Orange Trumpit	1	Fruit Tree	D	Shrubs	004	2021	Living	12	3
Blue Bells	1	Fruit Tree	D	Shrubs	005	2020	Unplanted	12	3

Example Output Data

Example output

Answer 1

Here is one way:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Variety", type text}, {"Block", Int64.Type}, {"BlockNm", type text}, {"RowN", type text}, {"RowNm", type text}, {"Loc", Int64.Type}, {"Planting Year", type text}, {"Status", type text}, {"RowSp", Int64.Type}, {"LocSp", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "dataName", each [Variety] &" - "&[Planting Year]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "colName", each [RowN]&": "&[RowNm]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Variety", "Block", "BlockNm", "RowN", "RowNm", "Planting Year", "Status", "RowSp", "LocSp"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[colName]), "colName", "dataName"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Loc", "D: Shrubs", "C: Tall Trees", "B: Vines", "A: Short Trees"})
in
    #"Reordered Columns"

All of these can can be done with ribbon commands; here is the output:

The steps are:

• source: original table
• Changed Type: auto change type, but I changed the date to text
• Added Custom: join columns Variety & Year
• Added Custom1: join columns rowN & rowNm
• Removed Columns: remove all columns except (Loc and 2 created custom columns)
• Pivoted Column: make rowN/rowNm columsn with Variety/year as data (advance don't aggregate)
• reorder columns to get into your column order

Getting this into the final format in Excel may be problematic. I don't want to make you go down another (potentially unprofitable) path, but added with your other ask you may need to consider some type of BI Analysis tool (power BI, Tableau, etc).