Power Query, Filtering Outliers Using IQR Method Before Averaging Values

[rageagainstjg]

I'm working on a Power Query within Excel to group data by customer and mine names and compute various aggregates such as total weight, total volume, average rock density, and so on. Here's a my existing code:

= Table.Group(#"Reordered Columns", {"Customer Name", "Mine Name"}, {
{"NF: Total Weight", each List.Sum([Total Weight]), type nullable number},
{"NF: Total Volume", each List.Sum([Total Volume]), type nullable number},
{"NF: Average Rock Density", each List.Average(List.Select([Rock Density], each _ <> null and _ <> 0)), type nullable number},
{"NF: Average Powder Factor Per Weight", each List.Average(List.Select([Powder Factor Per Weight], each _ <> null and _ <> 0)), type nullable number},
{"NF: Average Powder Factor Per Volume", each List.Average(List.Select([Powder Factor Per Volume], each _ <> null and _ <> 0)), type nullable number},
{"NF: Fuel Oil Total", each List.Sum([Fuel Oil Total]), type nullable number},
{"NF: Emulsion Weight Total", each List.Sum([Emulsion Weight Total]), type nullable number},
{"NF: Anfo Weight Total", each List.Sum([Anfo Weight Total]), type nullable number},
{"NF: Ammonium Nitrate Weight Total", each List.Sum([Ammonium Nitrate Weight Total]), type nullable number},
{"NF: Booster Total", each List.Sum([Booster Total]), type nullable number},
{"NF: Total Explosives Weight", each List.Sum([Total Explosives Weight]), type nullable number},
{"NF: Expected Vibration", each List.Average(List.Select([Expected Vibration], each _ <> null and _ <> 0)), type nullable number},
{"NF: Ppv", each List.Average(List.Select([Ppv], each _ <> null and _ <> 0)), type nullable number},
{"NF: Average Blast Latitude",
each List.Average(List.Select([Blast Latitude], each _ <> null and _ <> 0)),
type nullable number},
{"NF: Average Blast Longitude",
each List.Average(List.Select([Blast Longitude], each _ <> null and _ <> 0)),
type nullable number}
})

However, I want to enhance my calculation on the Blast Latitude and Blast Longitude columns by using the Interquartile Range (IQR) method to filter out outliers before averaging the remaining values. I tried implementing it as shown below:

let
Source = #"Reordered Columns",
Q1_Lat = List.Percentile(Source[Blast Latitude], 0.25),
Q3_Lat = List.Percentile(Source[Blast Latitude], 0.75),
IQR_Lat = Q3_Lat - Q1_Lat,
LowerBound_Lat = Q1_Lat - 1.5 * IQR_Lat,
UpperBound_Lat = Q3_Lat + 1.5 * IQR_Lat,
Q1_Long = List.Percentile(Source[Blast Longitude], 0.25),
Q3_Long = List.Percentile(Source[Blast Longitude], 0.75),
IQR_Long = Q3_Long - Q1_Long,
LowerBound_Long = Q1_Long - 1.5 * IQR_Long,
UpperBound_Long = Q3_Long + 1.5 * IQR_Long,
GroupedTable = Table.Group(Source, {"Customer Name", "Mine Name"}, {
{"NF: Total Weight", each List.Sum([Total Weight]), type nullable number},
{"NF: Total Volume", each List.Sum([Total Volume]), type nullable number},
{"NF: Average Rock Density", each List.Average(List.Select([Rock Density], each _ <> null and _ <> 0)), type nullable number},
{"NF: Average Powder Factor Per Weight", each List.Average(List.Select([Powder Factor Per Weight], each _ <> null and _ <> 0)), type nullable number},
{"NF: Average Powder Factor Per Volume", each List.Average(List.Select([Powder Factor Per Volume], each _ <> null and _ <> 0)), type nullable number},
{"NF: Fuel Oil Total", each List.Sum([Fuel Oil Total]), type nullable number},
{"NF: Emulsion Weight Total", each List.Sum([Emulsion Weight Total]), type nullable number},
{"NF: Anfo Weight Total", each List.Sum([Anfo Weight Total]), type nullable number},
{"NF: Ammonium Nitrate Weight Total", each List.Sum([Ammonium Nitrate Weight Total]), type nullable number},
{"NF: Booster Total", each List.Sum([Booster Total]), type nullable number},
{"NF: Total Explosives Weight", each List.Sum([Total Explosives Weight]), type nullable number},
{"NF: Expected Vibration", each List.Average(List.Select([Expected Vibration], each _ <> null and _ <> 0)), type nullable number},
{"NF: Ppv", each List.Average(List.Select([Ppv], each _ <> null and _ <> 0)), type nullable number},
{"NF: Average Blast Latitude",
each List.Average(List.Select([Blast Latitude],
each _ <> null and _ <> 0 and _ >= LowerBound_Lat and _ <= UpperBound_Lat)),
type nullable number},
{"NF: Average Blast Longitude",
each List.Average(List.Select([Blast Longitude],
each _ <> null and _ <> 0 and _ >= LowerBound_Long and _ <= UpperBound_Long)),
type nullable number}
})
in
GroupedTable

Unfortunately, when I attempt to run this code, nothing works, and I receive an error message.

Does anyone have any thoughts or guidance on how to correct this code? I appreciate any assistance you can provide!

 

[Peter_SSs]

A gentle reminder:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Power Query, Filtering Outliers Using IQR Method Before Averaging Values
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.


BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[smozgur]

I don't see any syntax error in the code. The problem could be at the Source side.

You need to identify which step fails first.
If it is the last step, GroupedTable, then the best debugging method that I can recommend is adding the aggregated columns one by one until you find the problem calculation.

If you could provide more details such as sample data (that also fails with the formula), error step and message, then it would help others to help you.