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
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