Adding a column identifying if above or below average of table

[shday98]

Happy New Year "Get Help with Power BI"!

I'm using Excel Power Query to extract elapsed time and the amount of air firefighters are consuming from their air bottle and need to add a column that will calculate whether the amount of air the firefighter consumed from their air bottle on the obstacle course is above the average of what all the firefighters who did the course consumed, below the average, or on average.

I tried creating a custom column named "Relative Air Consumed to Average" with an if statement:

if [Air Consumed] > List.Average(#Filtered Rows [Air Consumed]) then "Above" else ""

but I got this error:

Expression.Error: A cyclic reference was encountered during evaluation.

... which sounds to me like a circular reference type error.

I also tried creating another data query that calculated the average air consumed that I added to the model and then attempted this in a different custom column statement:

= Table.AddColumn(#"Filtered Rows", "Relative Air Consumed to Average", each if [Air Consumed] > List.Average(#"2023 Fit for Duty Data Load" [Air Consumed]) then "Above" else "On Average")

...but that gives a similar error.

Any thoughts on how I can:

1. Calculate the average PSI of Air Consumed
2. Use this average value to evaluate each row in my data table and identify of the row's Air Consumed value is above, at, or below this average?

[nickvanmaele · Accepted solution]

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. 

[shday98]

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. 

[Anonymous]

Before you add a custom column, add the List.Average step, then use that result in your   Table.AddColumn step. Let's say your current last step name is TheTable, which is Firefighter and Air Consumed columns. So:

AvgAir = List.Average(TheTable[Air Consumed])

AboveOrBelow = Table.AddColumn(TheTable, "Rank", each if [Air Consumed] > AvgAir then "Above" else "Below", type text)

--Nate

[shday98]

Pardon me in advance as I'm slow/new at this... here's what I put in the Advanced Editor in my Power Query Advanced Editor, where the last two lines before the "in" are based on your suggestion, but it didn't have any effect, is it something obvious that I'm doing wrong?

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")),
AvgAir=List.Average(#"Filtered Rows"[Air Consumed]),
AboveOrBelow = Table.AddColumn(#"Filtered Rows", "Rank", each if [Air Consumed] > AvgAir then "Above" else "Below", type text)
in
#"Filtered Rows"

[shday98]

Disregard @nickvanmaele ... I tweaked the code a little into what you see below and it worked!

Thank you !

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")),
AverageAir = List.Average(#"Filtered Rows"[Air Consumed]),
DifferenceWithAverage = Table.AddColumn(#"Filtered Rows","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 Type3" = Table.TransformColumnTypes(AboveBelow,{{"Performance vs average", type number}, {"Above or below average", type text}})

in
#"Changed Type3"

[AlB]

Hi @shday98 

Can you share some sample data in text-tabular format (so that the contents can be copied)? We can then build a possible solution for you

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[shday98]

Sure thing!

Data Table:

Name	Air Consumed
Firefighter A	500
Firefighter B	1000
Firefighter C	1500
Firefighter D	700

... so the average of this sample data is 925 PSI of Air Consumed... my goal is either a column added to the table that identifies whether each FireFighter's air consumption is Above, At Average, or Below that value.

Ideally looking like:

Name	Air Consumed	Relative to Average
Firefighter A	500	Below
Firefighter B	1000	Above
Firefighter C	1500	Above
Firefighter D	700	Below

... and if this is not possible because of the circular reference it causes, then a separate table that'll identify whether the scores from the obstacle course were Above/At/Below average such as:

Name	Relative to Average
Firefighter A	Below
Firefighter B	Above
Firefighter C	Above
Firefighter D	Below

... then I can just join this analysis table to be able to report which firefighters are above/at/below average.

Thanks!

[AlB]

@shday98 

See it all at work in the attached file. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsssSk3LTM8oSS1ScFTSUTI1MFCK1UEVdwKKGxpgkXAGSWDT4QKUMAeJxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Air Consumed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Air Consumed", Int64.Type}}),
    average_ = List.Average(#"Changed Type"[Air Consumed]),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Relative to average", each if [Air Consumed] > average_ then "Above" else "Below", type text)
in
   #"Added Custom"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[shday98]

Unfortunately I don't have Power BI, only Powere Query ... can this code work in Power Query as well?