Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Breaking summary value into detail calculated rows


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:

ActivityDuration (days)Value


Table 2:

ActivityHours per dayTotal Duration
A8=8x20 = 160 hrs
B10=10x30 = 300 hrs
C12=50x12 = 600 hrs


Table 1 needs to be converted to:

ActivityDuration (hrValue
A8 hr=(2000/160)*8
A8 hr=(2000/160)*8
A8 hr=(2000/160)*8
A8 hr=(2000/160)*8
A8 hr=(2000/160)*8
...up to 20 rows... up to 20 rows... up to 20 rows
B10 hr=(5000/300)*10
B10 hr=(5000/300)*10
B10 hr=(5000/300)*10
... up to 30 rows... up to 30 rows... up to 30 rows
C12 hr=(3000/600)*12
C12 hr=(3000/600)*12
C12 hr=(3000/600)*12
... up to 50 rows... up to 50 rows... up to 50 rows


Any suggestion / help is greatly appreciated.






Thank you so much, it worked perfectly. Thanks a lot.

View solution in original post

Super User
Super User

Try this:

    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"}})
    #"Renamed Columns"


Thank you so much, it worked perfectly. Thanks a lot.

Super User
Super User

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.

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors