PQ pivot or grouping by category

[PippaThePointer]

Hi,
I am having a great time learing PQ and think its awesome, but im stuck on something that seems so simple but i can work it out.
I have some data that i have updated to be tabulated so there is one row for every item. The rows a column for 'Store' and then a whole lot of other columns such as 'Size', category, ArtworkID and qty for example. I want to group the data so that i have only one row per 'Store' and then a column that contains the qty with headers of that column containing the 'Size', Category etc. My full data has loads of items but a i have test file here that should be enough to grasp what i have to do. I tried pivoting and grouping and a combination but just cant quite get it.
Here is a basic example:
Store Category QTY ArtworkCode
A Women 1 Woman_1_1200x3000.pdf
B Women 2 Woman_1_1200x2000.pdf
C Women 3 Woman_1_2000x3000.pdf
C Men 4 Man_1_2000x3000.pdf
C Women 5 Woman_1_1200x2000.pdf
C Men 6 Man_1_1200x2000.pdf
C Women 7 Woman_1_2300x3300.pdf
C Men 8 Man_1_2300x3300.pdf

And this is what i want it to be:

Woman_1_1200x3000.pdf	Woman_1_1200x2000.pdf	Woman_1_2000x3000.pdf	Man_1_2000x3000.pdf	Woman_1_1200x2000.pdf	Man_1_1200x2000.pdf	Woman_1_2300x3300.pdf	Man_1_2300x3300.pdf
Women	Women	Women	Men	Women	Men	Women	Men
A	1
B	2
C	3	4	5	6	7	8

 

[AlienSx]

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]), 
    stores = Table.Group(Source[[Store], [QTY]], "Store", {"Qty", (x) => x[QTY]}),
    result = Table.FromList(
        {Source[ArtworkCode]} & {Source[Category]} & Table.ToList(stores, (x) => {x{0}} & x{1}), 
        (x) => x, 
        Table.RowCount(Source), null, null
    )
in
    result

 

[PippaThePointer]

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    stores = Table.Group(Source[[Store], [QTY]], "Store", {"Qty", (x) => x[QTY]}),
    result = Table.FromList(
        {Source[ArtworkCode]} & {Source[Category]} & Table.ToList(stores, (x) => {x{0}} & x{1}),
        (x) => x,
        Table.RowCount(Source), null, null
    )
in
    result

Thanks for this. Its helpful.
The 2 header rows are currently starting at column 1 right above the 'Store'. I need these to start in this example at column2. So A2 and A3 would be empty cells or be filled with 'ArtworkCode' and 'Category' for example.
I would like the Columns to show only the unique amount required. Currently its giving me a column for every source row.

The end user wants to view this very long table, i have already created a macro in another test that will use a PQ dynamic drop down list so they can select a store and it will only show the row and the columns with an entry.

 

[PippaThePointer]

I think i have answered my own question with a small test using Pivot and transpose.
let
Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Test"]}[Content]),
PivotQTY = Table.Pivot(Source, List.Distinct(Source[Store]), "Store", "QTY", List.Sum),
DemotedHeaders = Table.DemoteHeaders(PivotQTY),
Reordered = Table.ReorderColumns(DemotedHeaders,{"Column2", "Column1", "Column3", "Column4", "Column5"}),
Transpose = Table.Transpose(Reordered),
PromoteHeaders = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true])
in
PromoteHeaders