Forum Discussion

Taffalaffa's avatar
Taffalaffa
Helper I
2 years ago
Solved

Convert a list into columns in a table

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=sndqkrlnd1s1wj5xf7511vnz1&dl=0

 

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.

Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

     

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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.