Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 #####################
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.