Convert a list into columns in a table

[Taffalaffa]

Hi.  I have the following table which is called activitycodeassignment.  This is just a small filtered version for the purposes of my question.  There is the a column that has the Code Category (there over fifty different categories) and then there is a column that has the code description.  I want to turn the Codes into columns with the description as the value for that category.  Here is what I am starting with:

I would like to end with one row per project & activity ID with each Code Category becoming it's own column and the code value populating appropriately based on the category:

I have tried transposing, grouping... I just can't figure it out.  Any help would be greatly appreciated!!

Here is a link to the PBI tables:

https://www.dropbox.com/scl/fi/is1bz53ocmkeesnc7u5ro/Column-to-List.pbix?rlkey=sndqkrlnd1s1wj5xf7511...

@ImkeF @v-chuncz-msft @parry2k @Ritaf1983 It looks like you may have some knowledge on how to do this based on similar posts but I couldn't quite adapt your other solutions to my issue.

[Ashish_Mathur · Accepted solution]

Hi,

This code works

let
    Source = Excel.Workbook(File.Contents("C:\Users\mathu\Desktop\Try.xlsx"), null, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Code ID"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Code Category"]), "Code Category", "Code Value")
in
    #"Pivoted Column"

Hope this helps.

[Taffalaffa]

Thank you so much Ashish!  This is spectacular! 

[Ashish_Mathur]

You are welcome.