Pivot with Multiple Columns?

[Scott4850]

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:

[dufoq3]

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]

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!

[dufoq3]

The link is working.

[Scott4850]

Ok, corporate block or something.  I've got it on a personal device.

[AlienSx · Accepted solution]

    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]

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]

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]

[AlienSx]

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

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.

[Scott4850]

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

[lbendlin]

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

[Scott4850]

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