Help with Power Query - Table Column

[Anonymous]

My second question for the day.. Hope someone can dispense some advice. Not looking for someone to do my work for me here (feel free tho) but any blogs, guides or starting tips about this topic would be great.

In my model I have a few tables, and two of them are related to each other in a one-to-many relationship baed on a text column (which is a formatted date) via Merge Query.

The column in question is called Accelo Available Hours. Here's a screen shot:

So, what I want to do in the top table, is add a few columns using data from the bottom one. I want to calculate the number of FTEs available on a given day by summing the available_hours column from the bottom table and dividing by the count of the rows, then dividing by 8. I want to do that with the whole table, then filtered by the 3 groups, either by group_id or Group.GroupName, resulting 4 new columns in the top table.

Can someone point me somewhere to get started with this kind of query, or give me a hint to get going?

Thanks.

[ImkeF · Accepted solution]

Hi

if you add a column to your top table with the following formula: Table.RowCount( [Accelo Available Hours]), then you'd get your first number. 

To add numbers from a column, you first have to convert it into a list: [Accelo Available Hours][available_hours]

Then wrap that list into the function that sums it up like so: List.Sum( [Accelo Available Hours][available_hours] )

To do this on a group-level, I'd recommend to create a new query where you pick out one of those tables, apply the transformations and turn this into a function (with the table as an input parameter). Then apply that function in the top table.

[Anonymous]

@ImkeF  Excellent, works beautifully, thanks.

This is the formula I used for my new column to get the total available FTEs:

((List.Sum ([Accelo Available Hours][available_hours]) / Table.RowCount( [Accelo Available Hours]) ) / 8 ) * Table.RowCount( [Accelo Available Hours])

Next up to figure out filtering by group.