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