Forum Discussion

pquintela's avatar
pquintela
New Member
2 years ago
Solved

Distributing total hours by month from start date

Hi guys. I'm desperate for help.

 

I don't know how many hours I've already spent looking into this, and you are my last resort.

 

I've a table with the folowing data:

IDAbs codAbs descHoursDaily HoursDaysStart
69100496090Vacation8,008130/12/2022
69100576100Vacation8,008130/12/2022
69100666090Vacation8,008130/12/2022
69101136090Vacation8,008130/12/2022
69101376090Vacation24,008330/12/2022
69104196080Maternity Leave757,507,510106/08/2022

 

How can I add the columns at the right with the splitting of hours by the avaiable days by month? 

 

IDAbs codAbs descHoursDaily HoursDaysStart01/08/202201/09/202201/10/202201/11/202201/12/202201/01/2023
69100496090Vacation8,008130/12/2022    8 
69100576100Vacation8,008130/12/2022    8 
69100666090Vacation8,008130/12/2022    8 
69101136090Vacation8,008130/12/2022    8 
69101376090Vacation24,008330/12/2022    168
69104196080Maternity Leave757,507,510106/08/2022195217,5225120  

 

After I get them, I know i'll have to unpivot them, but that's the last step.

 

As a side note, the table will have more data with dates from 2022 to the future, and I've already created a Calendar Table.

 

I've tryed a lot of things on PowerQuery, but I really don't master M language.

 

Can anybody help? 

 

Thanks for you help in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, pquintela 

     

    For M language, you may be able to try the following expressions and modify them based on your data:

    let
    Source = Excel.Workbook(File.Contents("C:\\Users\\v-yohua\\Documents\\Distributing total hours by month from start date.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", Int64.Type}, {"Abs cod", Int64.Type}, {"Abs desc", type text}, {"Hours", type number}, {"Daily Hours", type number}, {"Days", Int64.Type}, {"Start", type date}}),
    AddEndDate = Table.AddColumn(#"Changed Type", "End", each Date.AddDays([Start], [Days]-1)),
    AddDateList = Table.AddColumn(AddEndDate, "DateList", each {Number.From([Start])..Number.From([End])}),
    ExpandDateList = Table.ExpandListColumn(AddDateList, "DateList"),
    ChangeToDate = Table.TransformColumnTypes(ExpandDateList,{{"DateList", type date}})
in
    ChangeToDate

     

    This code will add an "End" column to your table that represents the end date of each ID. It then creates a "DateList" column that contains each day from the start date to the end date. Finally, it expands the DateList column into new rows, with each row corresponding to a date

     

    How to Get Your Question Answered Quickly 

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, pquintela 

     

    For M language, you may be able to try the following expressions and modify them based on your data:

    let
    Source = Excel.Workbook(File.Contents("C:\\Users\\v-yohua\\Documents\\Distributing total hours by month from start date.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"ID", Int64.Type}, {"Abs cod", Int64.Type}, {"Abs desc", type text}, {"Hours", type number}, {"Daily Hours", type number}, {"Days", Int64.Type}, {"Start", type date}}),
    AddEndDate = Table.AddColumn(#"Changed Type", "End", each Date.AddDays([Start], [Days]-1)),
    AddDateList = Table.AddColumn(AddEndDate, "DateList", each {Number.From([Start])..Number.From([End])}),
    ExpandDateList = Table.ExpandListColumn(AddDateList, "DateList"),
    ChangeToDate = Table.TransformColumnTypes(ExpandDateList,{{"DateList", type date}})
in
    ChangeToDate

     

    This code will add an "End" column to your table that represents the end date of each ID. It then creates a "DateList" column that contains each day from the start date to the end date. Finally, it expands the DateList column into new rows, with each row corresponding to a date

     

    How to Get Your Question Answered Quickly 

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.