Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Taffalaffa
Helper I
Helper I

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:

Taffalaffa_2-1696109441566.png

 

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:

Taffalaffa_3-1696109493869.png

 

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.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_1-1696127453413.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_1-1696127453413.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much Ashish!  This is spectacular! 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.