Pivot issue

I deleted some rows to test the different numbers.

You can try to understand the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSVVwVNJRSkxUitXBw09KQuUnJ+PmO6Hpd0LTj42PrN8ZTZ4YfmoqKj8tDcF3AfLLy1H5yPa5oLkXGx+kPxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SITE = _t, CODE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SITE", type text}, {"CODE", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SITE"}, {{"Rows", each _, type table [SITE=nullable text, CODE=nullable text]}}), 
    MaxCount = List.Max(List.Transform(#"Grouped Rows"[Rows], Table.RowCount)), 
    // Column name prefix: CODE
    ColumnNames = List.Transform({1..MaxCount}, each "CODE" & Text.From(_)), 
    result = Table.TransformColumns(
        #"Grouped Rows", 
        {
            "Rows", 
            each Record.FromList([CODE], List.FirstN(ColumnNames, Table.RowCount(_)))
        }
    ),
    #"Expanded Rows" = Table.ExpandRecordColumn(result, "Rows", ColumnNames, ColumnNames)
in
    #"Expanded Rows"

Hi naninamu, another solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSVVwVNJRSkxUitXBw09KQuUnJ+PmO6Hpd0LTTwwf2TxnNHli+KmpqPy0NATfBcgvL0flI9vnguZ+bHyQ/lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SITE = _t, CODE = _t]),
    Transformed = [ a = Table.FromList(Table.Group(Source, {"SITE"}, {{"T", each {_{0}[SITE]} & [CODE] }}, GroupKind.Local)[T], (x)=> x),
    b = Table.ColumnNames(a),
    c = Table.RenameColumns(a, List.Zip({ b, {"SITE"} & List.Transform(List.Skip(List.Positions(b)), each "CODE " & Text.From(_)) }))
  ][c]
in
    Transformed

Hi naninamu , here's another solution for you to look at without actually having to use the Pivot in the UI of PQ. Rather than that using Table functions to get through it. I'll add the snippet of the M code used and the image of the output for reference. Thanks!

Hi naninamu 

You can copy the following code into the avanced editor or see the attached file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSVVwVNJRSkxUitXBw09KQuUnJ+PmO6Hpd0LTj42PrN8ZTZ4YfmoqKj8tDcF3AfLLy1H5yPa5oLkXGx+kPxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SITE = _t, CODE = _t]),
    #"Grouped Rows" = Table.Group(Source, {"SITE"}, {{"Rows",  each Table.Transpose(Table.RemoveColumns(_,"SITE"))}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"})
in
    #"Expanded Rows"

Thank you so much ZhangKun  - I never would have come up with that! I didn't realise it would be so complicated.

I have tested it on my set of test data by changing the source, and it's working well.

I'm now trying to use it on my set of real data - it already has numerous transformation steps applied, and currently I can't get it to work.

I assume I paste yours in from the #"Changed Type step, and adjust the names of the 2 columns? It's also saying it doesn't recognise what "Rows" is, but I'm unsure if I should be changing or leaving that.

Many thanks once again!

Thank you LDufour  - I will give it a go this morning. Much appreciated.

Hi Omid_Motamedise  -many thanks. However, SITEs can have a varying number of CODEs. My example was not a good one - but SITE 1 could have 3 codes, SITE 2 50 codes, SITE 3 4 codes etc etc. So I need the solution to be able to accommodate the max number of Codes. Are you able to adjust that for me? Cheers!

I just wanted to thank everyone who took the time to give me code - they all worked although ultimately I had to pick one to integrate into my fuller solution! I appreciate the time everyone took to help me out.