Search code examples
powerquerydate-rangem

Power Query, avg value based on the values appearing within a specified date range

Context:

I have a data set for the weights of truck and trailer combinations coming into my site over the span of a few years. I have organized my data by seasons as I am trying to prove that the truck:trailers in winter are noticeably heavier due to ice, snow, and mud. The theory is, if the tare weight is higher in this season (the weight of the truck after it empties its load) than its Avg tare weight (which I need to calculate from the data) it can be deduced that the truck:trailer combinations are coming in with extra weight that we pay for in part as some snow/ice/mud falls off in the trailer emptying process.

What I've done so far:

  • I've defined a custom date range for my seasons
  • I've grouped Truck:Trailer by: count to get a duplicates column and, all rows to keep all my details
    • I've filtered out every combination I've seen less than 50 times, as i want good representation for each truck:trailer combo so that I can better emphasize repeated patterns
    • I've added an index column to better keep track of the individuals before expanding the details

What I need to do:

  • I only want to work with truck:trailer combinations which have weighed in for all four seasons at least once
  • I need to find the average tare weight of the truck:trailer combinations based over the extended range for both summer and autumn (the dry time of the year) while preserving the raw tare data for all seasons, as I need to eventually compare the winter tare values to this average.

example of my data

When I'm finished I'd like the data to look something like this

Pivot Chart

query data

Solution

  • For your first question (all seasons) you can add a column that holds the distinct count of the values in [Season] for each [Driver:Trailer]. Then filter your table on that column, keeping only the 4's. To achieve this, add the following m-code to your script in the Advanced Editor. Change the part after in to #"DistinctCount Season"

    #"DistinctCount Season" = Table.Join(#"insert name previous step","Driver:Trailer",
    Table.Group(#"insert name previous step", {"Driver:Trailer"}, 
    {{"DistinctCountSeasons", each Table.RowCount(Table.Distinct(_,"Season")),
    type number}}),"Driver:Trailer")

    Insert the name of your previous step where indicated.

    For second question:

    You can use a matrix-visual for that in you report. First create a measure:

    [AverageTare] = AVERAGE(table'[Tare])

    Then put [Season] on Rows and the [AverageTare] on Values. You can create a group (right-click on [Season] in the FIELDS-pain) called [DrySeason], to combine the values for Spring and Summer.
    If that doesn't work for you, explore the AVERAGEX function.

    EDIT
    In excel you can use a pivottable. Put [Season] on Rows and the [AverageTare] on Values. Right-click a value in the pivottable. Select Value Field Setting and choose Average. Then select the Seasons you want to group, right-click and select Group.

    EDIT 2
    To add a column in the Power Query Editor that holds the average [Tare] for the [Season] in each row, add the following steps to your script in the Avanced Editor:

    #"GroupedSeasonAvg" = Table.Group(#"Insert name previous step", {"Season"}, {{"AVG", each List.Average([Tare]), type number}}),
#"JoinOnSeason" = Table.NestedJoin(#"Insert name previous step",{"Season"},GroupedSeasonAvg,{"Season"},"AVGGrouped"),
#"ExtractSeasonAVG" = Table.ExpandTableColumn(JoinOnSeason, "AVGGrouped", {"AVG"}, {"SeasonAVG"})

    It works something like this:

    1. "GroupedSeasonAvg" : Creates a table with the avereges for each [Season]
    2. "JoinOnSeason": Creates a new column with tables joining the [Season] value for each row to [Season] in the grouped table.
    3. #"ExtractSeasonAVG": Expand each table and keep only [AVG].