Forum Discussion
bhm
4 years agoFrequent 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 p...
- 4 years ago
Thank you so much, it worked perfectly. Thanks a lot.
AlexisOlson
4 years agoSuper 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"
- bhm4 years agoFrequent Visitor
Thank you so much, it worked perfectly. Thanks a lot.