Skip to main content
cancel
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

Reply
bhm
Frequent Visitor

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:

ActivityDuration (days)Value
A202000
B305000
C503000

 

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.

 

Thanks

BHM

 

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

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.

wdx223_Daniel
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

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors