Forum Discussion

Scott4850's avatar
Scott4850
Frequent Visitor
2 years ago
Solved

Pivot with Multiple Columns?

Obviously I'm not very knowledgeable about this, but I did think I could do this fairly easily with a Pivot, UnPivot, or Transpose...or some combination therof.  But I am stumped.

 

I can pivot on Task, but with only Start or Finish, not both.  I tried duplicating Task and Pivoting again, but results in errors.  Actually, I think I've tried about 50 combinations.  Thanks for your attention.

 

Given the top table, I need the bottom:

 

 

 

 

 

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago
        fx = (tbl) => #table(
        {"Project"} & List.Combine(List.Transform(tbl[Task], (x) => {x & " (Start)", x & " (Finish)"})),
        {{tbl{0}[Project]} & List.Combine(Table.ToRows(tbl[[Start], [Finish]]))}
    ),
    group = Table.Group(
        your_table,
        "Project", 
        {"x", fx}
    ),
    combine = Table.Combine(group[x])

13 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    There is no need to pivot this. You can achieve the desired outcome with a standard matrix visual.

    • Scott4850's avatar
      Scott4850
      Frequent Visitor
      2 years ago

      But this is not going in a visual.  I do need to learn more about the matrix visual, though, so thanks!

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago
        fx = (tbl) => #table(
        {"Project"} & List.Combine(List.Transform(tbl[Task], (x) => {x & " (Start)", x & " (Finish)"})),
        {{tbl{0}[Project]} & List.Combine(Table.ToRows(tbl[[Start], [Finish]]))}
    ),
    group = Table.Group(
        your_table,
        "Project", 
        {"x", fx}
    ),
    combine = Table.Combine(group[x])
    • Scott4850's avatar
      Scott4850
      Frequent Visitor
      2 years ago

      Thanks, I'll post back when I have a chance to try it.

    • Scott4850's avatar
      Scott4850
      Frequent Visitor
      2 years ago

      Thanks, I think I can kinda understand this one, but I keep getting 'expected EOF' at the comma at the end of fx = (tbl) => #table(...),

       

      Also, just to make sure I understand, your_table is the name of my table from the previous step, which in my case is "Renamed Columns", so I would have #"Renamed Columns" there, correct?

      • AlienSx's avatar
        AlienSx
        Super User
        2 years ago

        expected EOF - syntax error, show your full code. Kind of "let" is missing or something. 

        your_table: yes, that's correct. You should replace this by the name of your (stand alone) query or last step in your code. 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Scott4850, different approach here:

     

    Result

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBU0lEKgRDBJYlFJWCWW2ZeZnGGUqwOTIERmIArMEJWYETIBCN8JsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Task = _t, Start = _t, Finish = _t]),
    GroupedRows = Table.Group(Source, {"Project"}, {{"All", each 
        [ colNames = List.Combine(List.Transform([Task], each { _ & " (Start)", _ & " (Finish)" })),
          transform = Table.FromRows( { {[Project]{0}} & List.Combine(Table.ToRows(Table.SelectColumns(_, {"Start", "Finish"}))) }, {"Project"} & colNames ),
          changedType = Table.TransformColumnTypes(transform, List.Transform({"Project"} & colNames, (x)=> {x, type text} ))
        ][changedType]
, type table}}),
    All = Table.Combine(GroupedRows[All])
in
    All
    • Scott4850's avatar
      Scott4850
      Frequent Visitor
      2 years ago

      Thanks for the tips on posting!  Couldn't get the table control to work even with tips, though 🙂  Updated original post with a pic instead.

       

      I'm unable to try the solution at this time, but will post back later.  Note the link to your explanation is not working, at least at the moment.  Thanks!