cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
willpage Frequent Visitor
Frequent Visitor

Help with Power Query - Table Column

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:

 

image.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Help with Power Query - Table Column

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.

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: Help with Power Query - Table Column

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.

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

willpage Frequent Visitor
Frequent Visitor

Re: Help with Power Query - Table Column

@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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors