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 ...
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])
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. 

Scott4850's avatar
Scott4850
Frequent Visitor
2 years ago

 

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    Scott4850 i did not change anything, just added your table definition. Watch this. 

    let
    // your_tabel definition
    your_table = #table(
        {"Project", "Task", "Start", "Finish"},
        {{"P1", "T1", "T1Start", "T1Finish"},
        {"P1", "T2", "T2Start", "T2Finish"},
        {"P2", "T1", "T1Start", "T1Finish"},
        {"P2", "T2", "T2Start", "T2Finish"}}),

    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])
in
    combine

     

    • Scott4850's avatar
      Scott4850
      Frequent Visitor
      2 years ago

      Thanks!  Your solution is succinct and intuitive (even if I don't fully understand it yet).  And, it works!

       

      And thanks so much for your patience.  I'd never done a custom query, btw, so that is the reason it took the extra effort.