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

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's avatar
naninamu
Icon for Helper IV rankHelper IV
1 year ago

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

    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's avatar
      naninamu
      Icon for Helper IV rankHelper IV
      1 year ago

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

        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"