Forum Discussion

naninamu's avatar
naninamu
Icon for Helper IV rankHelper IV
1 year ago
Solved

Pivot issue

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 Si...
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    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
ZhangKun's avatar
ZhangKun
Icon for Super User rankSuper User
1 year ago

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"