Power query: transform only one column into header and bring values from another column into corresponding columns

Question

I may not describe well my question. I would like to only transform one column and make that column's values as new column header. And the cell value for these new columns will be based from another column.

I have a database like below:

Ref number	Task name	Task completed date
Ref1234	task1	02/12/2023
Ref1234	task2	03/05/2022
Ref1234	task3	02/11/2023
Ref1233	task1	03/05/2023
Ref1233	task2	02/02/2021
Ref1233	task3	03/10/2022

How can I use power query to change into this table:

Ref number	task1	task2	task3
Ref1234	02/12/2023	03/05/2022	02/11/2023
Ref1233	03/05/2023	02/02/2021	03/10/2022

I tried unpivot, transform, and pivot but none of them work properly. I have quite big database.

Many thanks!

Answer 1

I assume your original table is called "Table1", below code should work:

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  tasks = List.Distinct(Source[#"Task name"]),
  grouped = Table.Group(Source, {"Ref number"}, {"TaskInfos", each Record.FromList([#"Task completed date"], [#"Task name"])}),
  AddSubTable = Table.AddColumn(grouped, "SubTable", each Table.FromRecords({Record.AddField([TaskInfos], "Ref number", [Ref number])})),
  FilteredColumns = Table.SelectColumns(AddSubTable, {"SubTable"}),
  Expanded = Table.ExpandTableColumn(FilteredColumns, "SubTable", {"Ref number"}&tasks, {"Ref number"}&tasks)
in
  Expanded

As horseyride suggested, I changed the last step to Table.ExpandTableColumn. it has better control over the order the columns, and makes the code cleaner.

It groups your original table be ref number, and create a sub table based on each group's task names and task completed dates.