Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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.
User | Count |
---|---|
80 | |
77 | |
64 | |
48 | |
45 |
User | Count |
---|---|
103 | |
45 | |
39 | |
39 | |
36 |