Forum Discussion

naninamu's avatar
naninamu
Helper 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
naninamu's avatar
naninamu
Helper 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
Super 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
    Helper 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
      Super 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"