Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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:
@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.
Solved! Go to 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.
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.
Thank you so much Ashish! This is spectacular!
You are welcome.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |