I have a particular case to unpivot 8 different columns into 2 columns. I have a table in excel School with the four subjects with scores and %age.
I used unpivot-selected columns on Math, Bio, Social and Absent (count) to convert the 12 columns into 11 and 16 rows.
But how to get the unpivot the remaining 4 columns of %age to get the columns converted from 11 to 7 columns and 16 rows?
Please help how to unpivot.
Thanks, NewB
I would just add a custom column.
This is M-Code accessed from Home => Advanced Editor
Examine the Applied Steps and the code comments to better understand what it is doing.
let
//change next line to reflect your actual data source (Table name)
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
//set data types
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"School", type text}, {"Class", Int64.Type}, {"Group", type text}, {"EOM", type date},
{"Math", Int64.Type}, {"Bio", Int64.Type}, {"Social", Int64.Type}, {"Absent", Int64.Type},
{"Math %", Int64.Type}, {"Bio%", Int64.Type}, {"Social%", Int64.Type}, {"Absent%", Int64.Type}}),
//Unpivot the Scores columns (Math, Bio, Social, Absent)
//Note the resulting two columns are named Subject and Score
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type",
{"School", "Class", "Group", "EOM", "Math %", "Bio%", "Social%", "Absent%"},
"Subject", "Score"),
//Add an Index column so the Custom Column can refer to the correct row
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 0, 1, Int64.Type),
//Add Custom column to return the appropriate percentage
// This relies on the name similarity between Subject and Percentage: eg Math => Math %
// but you could also do a bunch of If Then statements
#"Added Custom" = Table.AddColumn(#"Added Index", "%age", each
Table.Column(#"Added Index", List.Select(Table.ColumnNames(#"Unpivoted Columns"),
(li)=> Text.Contains(li,[Subject])){0}){[Index]}, Int64.Type),
//Remove the unwanted columns
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Math %", "Bio%", "Social%", "Absent%", "Index"})
in
#"Removed Columns"
Instead of the above, you could just, at the end of your existing code, select to Add Conditional Column. The dialog box would look like:
and the M-Code:
let
//change next line to reflect your actual data source (Table name)
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
//set data types
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"School", type text}, {"Class", Int64.Type}, {"Group", type text}, {"EOM", type date},
{"Math", Int64.Type}, {"Bio", Int64.Type}, {"Social", Int64.Type}, {"Absent", Int64.Type},
{"Math %", Int64.Type}, {"Bio%", Int64.Type}, {"Social%", Int64.Type}, {"Absent%", Int64.Type}}),
//Unpivot the Scores columns (Math, Bio, Social, Absent)
//Note the resulting two columns are named Subject and Score
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type",
{"School", "Class", "Group", "EOM", "Math %", "Bio%", "Social%", "Absent%"},
"Subject", "Score"),
//add Custom Column with multiple `if then else if` clauses
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "%age", each
if [Subject] = "Math" then [#"Math %"]
else if [Subject] = "Bio" then [#"Bio%"]
else if [Subject] = "Social" then [#"Social%"]
else if [Subject] = "Absent" then [#"Absent%"]
else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Math %", "Bio%", "Social%", "Absent%"})
in
#"Removed Columns"
The advantage of the second method is that it is more obvious what is going on. However, if you add/delete/change courses and corresponding percentages, you will need to edit the code. With the first, you won't have to in that particular step. You will need to edit code in other steps, but those are more easily changed to be course-name independent.
