Forum Discussion
Breaking summary value into detail calculated rows
Hi,
I am looking for some solutions to the below problem. There is a list of activities, which has durations in days and value either in $ or in hours (Table 1).
Another table shows working hours per day for each activity ( 8 hours or 10 hours per day) (Table 2)
Now I want to convert the duration into hours based on the 2nd table reference, then expand these activities and values per 8 or 10 hour (based on the 2nd table). Something like below:
Table 1:
| Activity | Duration (days) | Value |
| A | 20 | 2000 |
| B | 30 | 5000 |
| C | 50 | 3000 |
Table 2:
| Activity | Hours per day | Total Duration |
| A | 8 | =8x20 = 160 hrs |
| B | 10 | =10x30 = 300 hrs |
| C | 12 | =50x12 = 600 hrs |
Table 1 needs to be converted to:
| Activity | Duration (hr | Value |
| A | 8 hr | =(2000/160)*8 |
| A | 8 hr | =(2000/160)*8 |
| A | 8 hr | =(2000/160)*8 |
| A | 8 hr | =(2000/160)*8 |
| A | 8 hr | =(2000/160)*8 |
| ...up to 20 rows | ... up to 20 rows | ... up to 20 rows |
| B | 10 hr | =(5000/300)*10 |
| B | 10 hr | =(5000/300)*10 |
| B | 10 hr | =(5000/300)*10 |
| ... up to 30 rows | ... up to 30 rows | ... up to 30 rows |
| C | 12 hr | =(3000/600)*12 |
| C | 12 hr | =(3000/600)*12 |
| C | 12 hr | =(3000/600)*12 |
| ... up to 50 rows | ... up to 50 rows | ... up to 50 rows |
Any suggestion / help is greatly appreciated.
Thanks
BHM
Thank you so much, it worked perfectly. Thanks a lot.
4 Replies
NewStep=#table({"Activity","Duration","Value"},List.TransformMany(Table.ToRows(Table2),each let a=Table1{[Activity=_{0}]}? in if a=null then {} else List.Repeat({List.FirstN(_,2)&{a[Value]/a[#"Duration(days)"]}},aa[#"Duration(days)"]),(x,y)=>y))
Thank you for your help.
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyABMGBkqxOtFKTkCOMUjEFCbiDOaAhUEisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activity = _t, #"Duration (days)" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity", type text}, {"Duration (days)", Int64.Type}, {"Value", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Activity"}, Table2, {"Activity"}, "Table2", JoinKind.LeftOuter), #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Hours per day"}, {"Hours per day"}), #"Added Custom" = Table.AddColumn(#"Expanded Table2", "DayNum", each {1..[#"Duration (days)"]}, type list), #"Added Custom1" = Table.AddColumn(#"Added Custom", "DailyValue", each [Value] / [#"Duration (days)"], type number), #"Expanded DayNum" = Table.ExpandListColumn(#"Added Custom1", "DayNum"), #"Removed Columns" = Table.RemoveColumns(#"Expanded DayNum",{"Duration (days)", "Value", "DayNum"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Hours per day", "Duration (hours)"}, {"DailyValue", "Value"}}) in #"Renamed Columns"Thank you so much, it worked perfectly. Thanks a lot.
