Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm quite new to Power BI and I try to query Azure DevOps data for a Board with historical data.
I have made an OData feed that queries data and returns the data below (see table) and for each row where IsCurrent = True, I want to calculate the "blocked time" into a new Column BlockedTime for that WorkItemId. So I need to traverse records for each WorkItemId, scan for text "Blocked" in field TagNames and do some date calculation.
I have specified three scenarios for the WorkItemId 1, 2 and 3.
I use the column Index as the number to reference lines in my calculations below.
My query returns the following:
WorkItemId Revision Index AnalyticsUpdatedDate IsCurrent TagNames BlockedTime 1 7 0 06/19/2020 11.41.04 True See calculation 1 1 6 1 06/19/2020 11.41.04 False Blocked 1 5 2 06/18/2020 10.41.23 False Blocked 1 4 3 06/17/2020 09.38.54 False 1 3 4 06/16/2020 14.22.21 False Blocked 1 2 5 06/15/2020 15.01.02 False 1 1 6 06/14/2020 07.21.16 False 2 6 7 07/07/2020 09:58:12 True Blocked See calculation 2 2 5 8 07/07/2020 09:58:12 False 2 4 9 07/06/2020 10:22:02 False Blocked 2 3 10 07/05/2020 12:34:31 False 2 2 11 07/04/2020 13:51:30 False Blocked 2 1 12 07/03/2020 08:23:41 False 3 1 13 07/02/2020 10:01:55 False Blocked See calculation 3
RULES
Variable CurrentDate contains current DateTime
When TagNames contains "Blocked" text for a given line, calculation for summing up total blocked time for the "BlockedTime" column in row where IsCurrent = True.
I've specified calculation examples below.
in AnalyticsUpdatedDate and the time should be calculated "backwards" by traversing down in the records (Revision number going down) and summarized until a line without "Blocked" in TagNames is encountered
Calculation 1: Calculate BLOCKED TIME and put the result into BlockedTime(0)
(AnalyticsUpdatedDate(1) - AnalyticsUpdatedDate(2)) + (AnalyticsUpdatedDate(2) - AnalyticsUpdatedDate(3)) + (AnalyticsUpdatedDate(4) - AnalyticsUpdatedDate(5))
Calculation 2: Calculate BLOCKED TIME and put the result into BlockedTime(7)
(CurrentDate - AnalyticsUpdatedDate(7)) + (AnalyticsUpdatedDate(9) - AnalyticsUpdatedDate(10)) + (AnalyticsUpdatedDate(11) - AnalyticsUpdatedDate(12))
Calculation 3: Calculate BLOCKED TIME and put the result into BlockedTime(13)
(CurrentDate - AnalyticsUpdatedDate(13))
Anyone who has any ideas of how this could be solved best?
here a draft on which you can make the necessary changes and adjustments ...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZI9DsIwDIXv0hkJ/8Rt6pGBE7AhBgRMICGBuD9OSIMpVQYrqeKvz+8l+32H3aobrMAKxzX0awLKH6gBFYJtd4/XxZbusPr09+m40b893p4J2Nzup+vlXDmxotQaPQeJI25w6Z/pHAfHwagcVbxeBbhA2HuhoERK2BCiMiSK50TBjNGSUE0j+MkGU1Hs5wCV5hy3WRmcFYmK9I3aDzYNFRvYn05yPxbAR20J/DiZC+WYoYBSQVIOyriklC8UCxEqwSqoDA2pnB8VkKupaG/BXsRciieCC0HOld2PyEJ6hzc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, Revision = _t, Index = _t, AnalyticsUpdatedDate = _t, IsCurrent = _t, TagNames = _t, BlockedTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"Revision", Int64.Type}, {"Index", Int64.Type}, {"AnalyticsUpdatedDate", type date}, {"IsCurrent", type text}, {"TagNames", type logical}, {"BlockedTime", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "calculations", each if [TagNames] then
let
tab=Table.SelectRows(#"Changed Type",(r)=> _[WorkItemId]=r[WorkItemId]),
currentDate= Date.From(DateTime.LocalNow()),
nrows=Table.RowCount(tab),
aud=tab[AnalyticsUpdatedDate],
bt=tab[BlockedTime],
tn=tab[TagNames],
zt=#duration(0,0,0,0)
in List.Accumulate({0..nrows-1},zt,(s,c)=>s+(if (bt{c}<>"" and tn{c}) then currentDate-aud{c} else if bt{c}<>"" then aud{c}-aud{c+1} else zt))
else "")
in
#"Added Custom"
#################### edited and adjusted #####################