Forum Discussion
Adding a column identifying if above or below average of table
Hi shday98 ,
Here is a solution that will work in Excel.
On a worksheet, ensure that you have a Table (CTRL+T) like this one:
Name Air Consumed Person A 500 Person B 1000 Person C 1500 Person D 700 Then, in Excel, click anywhere in the Table and choose "From Table/Range" in the Data ribbon.
This will start up Power Query and create a query based on that table.
Open up the Advanced Editor of that query, and paste in the code below:
Note: in the first line "Source = ...", replace "tblAirConsumption" in the code below by the name that your own table has in Excel.
let
Source = Excel.CurrentWorkbook(){[Name="tblAirConsumption"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Air Consumed", Int64.Type}}),
AverageAir = List.Average(#"Changed Type"[Air Consumed]),
DifferenceWithAverage = Table.AddColumn(#"Changed Type","Performance vs average", each [Air Consumed] - AverageAir),
AboveBelow = Table.AddColumn(DifferenceWithAverage, "Above or below average", each if [Air Consumed] > AverageAir then "Above avg" else "Below avg"),
#"Changed Type1" = Table.TransformColumnTypes(AboveBelow,{{"Performance vs average", type number}, {"Above or below average", type text}})
in
#"Changed Type1"That ought to give you two additional columns that show if a person is above or below the average, and by how much. You can then look at it step by step to see how it all works.
If this answers your question, please mark this answer as the solution.
Advocate IIHi shday98 ,
Here is a solution that will work in Excel.
On a worksheet, ensure that you have a Table (CTRL+T) like this one:
| Name | Air Consumed |
| Person A | 500 |
| Person B | 1000 |
| Person C | 1500 |
| Person D | 700 |
Then, in Excel, click anywhere in the Table and choose "From Table/Range" in the Data ribbon.
This will start up Power Query and create a query based on that table.
Open up the Advanced Editor of that query, and paste in the code below:
Note: in the first line "Source = ...", replace "tblAirConsumption" in the code below by the name that your own table has in Excel.
let
Source = Excel.CurrentWorkbook(){[Name="tblAirConsumption"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Air Consumed", Int64.Type}}),
AverageAir = List.Average(#"Changed Type"[Air Consumed]),
DifferenceWithAverage = Table.AddColumn(#"Changed Type","Performance vs average", each [Air Consumed] - AverageAir),
AboveBelow = Table.AddColumn(DifferenceWithAverage, "Above or below average", each if [Air Consumed] > AverageAir then "Above avg" else "Below avg"),
#"Changed Type1" = Table.TransformColumnTypes(AboveBelow,{{"Performance vs average", type number}, {"Above or below average", type text}})
in
#"Changed Type1"
That ought to give you two additional columns that show if a person is above or below the average, and by how much. You can then look at it step by step to see how it all works.
If this answers your question, please mark this answer as the solution.
Thank you nickvanmaele !
Below is the output from the advanced editor... I added your statements toward the bottom where you see Source2... I did this because I thought I needed the code above that point to consume the source data file and create the basic tables needed.
Unfortunately I get the error "Expression.Error: The column 'Name' of the table wasn't found." for the step "Changed Type3" while "Source2" seems to work fine.
Here's what's in the advanced editor... is there some glaring mistake I'm making (hopefully 🙂 )
let
Source = Csv.Document(File.Contents("C:\Users\shday\Downloads\2023 Fit for Duty.csv"),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Timestamp", type text}, {"Last ", type text}, {"First Name", type text}, {"Start PSI", Int64.Type}, {"End PSI", Int64.Type}, {"Time on Air", type duration}, {"Total Time", type duration}, {"Result", type text}, {"Proctor Name", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type"," EST","",Replacer.ReplaceText,{"Timestamp"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Timestamp", type datetime}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Timestamp", "Last ", "First Name", "Start PSI", "End PSI", "Result", "Proctor Name", "Time on Air", "Total Time"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Fiscal Year", each 2023),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Timestamp", "Last ", "First Name", "Start PSI", "End PSI", "Result", "Proctor Name", "Fiscal Year", "Time on Air", "Total Time"}),
#"Added Custom1" = Table.AddColumn(#"Reordered Columns1", "Last-First", each Text.Trim([#"Last "])&", "&Text.Trim([First Name])),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Custom1",{"Timestamp", "Last ", "First Name", "Last-First", "Start PSI", "End PSI", "Result", "Proctor Name", "Fiscal Year", "Time on Air", "Total Time"}),
#"Added Custom2" = Table.AddColumn(#"Reordered Columns2", "Air Consumed", each [Start PSI]-[End PSI]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Air Consumed", Int64.Type}}),
#"Reordered Columns3" = Table.ReorderColumns(#"Changed Type2",{"Timestamp", "Last ", "First Name", "Last-First", "Start PSI", "End PSI", "Air Consumed", "Result", "Proctor Name", "Fiscal Year", "Time on Air", "Total Time"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns3", each ([#"Last "] <> "test2")),
Source2=Excel.CurrentWorkbook(){[Name="_2023_Fit_for_Duty_Data_Load"]}[Content],
#"Changed Type3" = Table.TransformColumnTypes(Source2,{{"Name", type text}, {"Air Consumed", Int64.Type}}),
AverageAir = List.Average(#"Changed Type3"[Air Consumed]),
DifferenceWithAverage = Table.AddColumn(#"Changed Type3","Performance vs average", each [Air Consumed] - AverageAir),
AboveBelow = Table.AddColumn(DifferenceWithAverage, "Above or below average", each if [Air Consumed] > AverageAir then "Above avg" else "Below avg"),
#"Changed Type4" = Table.TransformColumnTypes(AboveBelow,{{"Performance vs average", type number}, {"Above or below average", type text}})in
#"Changed Type4"Thanks,
Stephen
- nickvanmaele
hi shday98
The code of my previous post only works in conjunction with the sample data table that I had included. My sample table had a column called "Name", and in manipulating this table, the column name "Name" has been hardcoded in the query steps of my solution.
The code that you copied from your Advanced Editor indicates that you are using another source file "2023 Fit for Duty.csv". That CSV file problably has different column headings. If there is no column called "Name" in that file, and if you copied my code into yours without modification, my code will try to do something to a column "Name" that your source file does not contain, hence an error will result.
Just to be clear, first try to open a completely new Excel file, paste the small sample table of my previous post into a worksheet, make sure that it is a Table (via CTRL+T), and follow the steps of my previous post. In the Power Query window of that new Excel file, the Advanced Editor should only contain the code that I have posted above, and it should not contain any of your previously existing code. This way, you will see how the solution works for the very small sample table that I have used.
Once you understand how it works, you can re-apply the same technique and insert your own step "AverageAir = List.Average ..." in the query that you have built with your real source file "2023 Fit for Duty.csv"
Give it a go and see how far you get.
