• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Aggregate a value across dates in a table

A

Aaron.Lowrey

New Member
I'm attempting to find a way to aggregate a value across several dates. I have a set of hours for projects that I am wanting to split up between all the dates for that project. I've been able to work out work days including holidays in a table column. I would like to use some function like list.aggregate to take the value say like 40 hours and set 8 hours in each row for each date until I run out of hours.

I've run into a dead end and I've not been able to figure out how to make this work. I am trying to do all of this using M. Any help would be appreciated.
 
Attach a workbook with 8-10 records showing what your source data looks like. Then mock up what you want the solution for those records to look like. Do not post a picture as we cannot manipulate data in a picture.
 
Thank you to anyone that can help me learn this. I am new to PQ.

My dates are sorted in descending order currently as I need to load the hours from the due date back towards the start date. We are kind of building it a bit backwards due to how we receive the report. I currently have an index that I am hoping to use on a secondary complication, but I am leaving that part out until I can figure out this first part.

I have attached a sample. The table I am building is a nested table in each row of a query so please pretend the example data is a query not excel data.
 

Attachments

  • Examples.xlsx
    13.5 KB · Views: 3
Cross-posted here
www.mrexcel.com

Help with list.accumulate

I have a table with dates in a power query column for multiple projects. The table has dates between the start date and end date that have been filtered to exclude weekends and holidays. So I have functional work dates. I would like to add a column to this table with prorated working hours...
www.mrexcel.com www.mrexcel.com
 
Aaron.Lowrey
Please reread Forum Rules, those rules are for You too.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
GraH - Guido said:
Cross-posted here
www.mrexcel.com

Help with list.accumulate

I have a table with dates in a power query column for multiple projects. The table has dates between the start date and end date that have been filtered to exclude weekends and holidays. So I have functional work dates. I would like to add a column to this table with prorated working hours...
www.mrexcel.com www.mrexcel.com
Click to expand...
Thank you for catching my mistake. I will attempt to do better in the future.
 
Can you confirm that MrExcel is the only other place you have asked this question (or one very like it)?

I can do something like the middle table below, from the left table and the Holidays table on the right:
80531
 
Thank you for your help. I am poorly explaining what I need help doing.

I have a table in a query column called Dates. It has dates for each project that are not weekends or holidays and it is sorted in descending order.

I have a total work hours value that I need to spread out in this sub table in a new column. With one twist; The first day in the series (last actual day) has a shifting time the work is due which i account for in this query with a [Workday Hours Offset].

I am sorry I am so bad at explaining what i need. I am figuring it out as I go.

i am struggling with not knowing M well enough to solve my problem or ask for help.

Is there a way to use List.Generate or List.Accumulate to create new columns in this table that perform if then or mathematical operations ?

Like, can I create a new column called Workday hours offset that files that value only at the 0 index and fills the rest as 0?
 

Attachments

  • 1660917382907.png
    1660917382907.png
    5.3 KB · Views: 5
Aaron.Lowrey said:
I have a table in a query column called Dates. It has dates for each project that are not weekends or holidays and it is sorted in descending order.
Click to expand...
In the workbook you attached the table with the column Dates each containing
has no query producing it so the column only contains the string [Table ] .

I'd need to see the file with the query in.

ps. did you notice in my picture there's a column headed Hrs (column M) containing differing hours per date?
 
Last edited:
You must log in or register to reply here.
Back
Top