Pivot issue

[naninamu]

Hello - this seems simple but I can't work it out.

I have a table like this:

SITE	CODE
Site A	aa
Site A	aa
Site A	bb
Site A	cc
Site A	cc
Site B	aa
Site B	bb
Site B	bb
Site B	bb
Site B	cc
Site C	bb
Site C	bb
Site C	bb
Site C	ee
Site C	ff
Site D	ww
Site D	cc
Site D	aa
Site D	aa
Site D	ww

I need it to get into this format:

SITE	CODE 1	CODE 2	CODE 3	CODE 4	CODE 5
Site A	aa	aa	bb	cc	cc
Site B	aa	bb	bb	bb	cc
Site C	bb	bb	bb	ee	ff
Site D	ww	cc	aa	aa	ww

Now, I know I need to add another column into the top table with Code 1, Code 2... etc... in Excel no issue, but in PQ I can't work out how to write it. This is a simplified example - in real life each site can vary greatly in number from 1 instance up to 100 instances.

Any help much appreciated! Thanks in advance

[naninamu]

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. 

[dufoq3]

You're welcome 😉

[naninamu]

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!

[Omid_Motamedise]

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"

[SundarRaj]

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!

[naninamu]

Hi @SundarRaj  - thanks so much for helping me out. Just looking at it (and I could be wrong) it appears you're hard coding in 5 Code columns? My example data was probably not the best... each Site can actually have a different number of Codes. So Site 1 could have 1 code, Site 2 could have 5 codes, Site 3 could have 8 codes etc etc. Can you adjustyour code at all to allow for this? THanks!!

[SundarRaj]

Hi @naninamu , yes you are right. I understand the requirement. I did make a few changes to my query. I'll share the snippet of the code here. Do have a look and don't hesitate to ping back. Thanks!

[naninamu]

Thanks for your help. I'm giving everyone's solutions a go later today - any chance you could put your code snipet in a form I can copy and paste? Thanks!!

[SundarRaj]

Absoutely @naninamu . Here you go! Thanks
let
Source = Excel.CurrentWorkbook(){[Name = "Table6"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"SITE", type text}, {"CODE", type text}}),

Table = #"Changed Type",
#"Grouped Rows" = Table.Group(Table, {"SITE"}, {{"All", each _[CODE], type table [SITE=nullable text, CODE=nullable text]}}),

// Numbers = List.Transform(#"Grouped Rows"[All], each List.Count(_)),

// ColNames = List.Split(List.Transform(List.Combine(List.Transform(Numbers, each {1.._})), each "CODE" & Text.From(_)),5),

TableList = Table.TransformColumns(#"Grouped Rows", {"All", each Table.FromRows({_})}),

NamesUpdated = Table.TransformColumns(TableList, {"All", each Table.RenameColumns(_,List.Zip({Table.Transpose(Table.DemoteHeaders(_))[Column1], List.Transform({1..Table.RowCount(Table.Transpose(_))}, each "CODE" & Text.From(_))}))}),

ExpandNames = List.Distinct(List.Combine(List.Transform(NamesUpdated[All],each Table.ColumnNames(_)))),

#"Expanded All" = Table.ExpandTableColumn(NamesUpdated, "All", ExpandNames, ExpandNames)

in
#"Expanded All"

[naninamu]

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

[naninamu]

Hi - it ran but came back with errors. The Site column had errors in every row, and it only made 2 columns even though most Sites had more than 2 codes. Sorry I'm not that great at understanding the code - does it handle the fact that each Site has a different number of Codes? ie some Sites might have 1 code, others could have 50 for instance. Thanks for your help!!

[dufoq3 · Accepted solution]

Hi @naninamu, I'm not sure who is LDufour, but you wanted to reply me probably 😉

updated code:

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.Group(Source, {"SITE"}, {{"T", each {_{0}[SITE]} & [CODE] }}, GroupKind.Local)[T],
    b = let max = List.Max(List.Transform(a, List.Count)) in Table.FromRows(List.Transform(a, each _ & List.Repeat({null}, max - List.Count(_)))),
    c = Table.ColumnNames(b),
    d = Table.RenameColumns(b, List.Zip({ c, {"SITE"} & List.Transform(List.Skip(List.Positions(c)), each "CODE " & Text.From(_)) }))
  ][d]
in
    Transformed

[naninamu]

Hi @dufoq3 - sorry yes was meant to be you! Not sure what happened there. Thanks for the above, will try to add it to my code in the morning. 

[dufoq3]

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

[naninamu]

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!

[ZhangKun]

maybe you can provide more complete information, especially the name of the columns that need to be processed. 

[ZhangKun]

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"