Forum Discussion
PowerBI Desktop
Hello PowerBI community @Amit, @Greg , @tamerj1 , @lbendlin, @amitchandak , @olgad , @Sahir_Maharaj , @FreemanZ , @tamerj1 , @Greg_Deckler @christinepayton @audreygerred @LukeB,...
Hello lbendlin ,
Sorry about the delayed response. I was caught up on some other urgent things. But here is the sample dataset.
I will more than appreciate any of your help.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY5LCoAwDESvIlkLSWrqZ+nvFKX3v4YdqlUR6S5vHjMkBJqpJXEsyk60B3QFYhtoQeRZDZEAhgLwK6IpV5wlUGH1GeC33L996l8AvyOyp7dX//ufVf4bK/8p57v52feV/XMM+/EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equipment = _t, CommissionedDate = _t, BreakdownDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CommissionedDate", type date}, {"BreakdownDate", type date}}), process = (tbl)=> let #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 then null else [BreakdownDate]-#"Added Index"[BreakdownDate]{[Index]-1}), #"Grouped Rows1" = Table.Group(Table.SelectRows(#"Added Custom", each ([Custom] <> null)), {"Equipment"}, {{"Avg", each List.Average([Custom]), type duration}}) in if Table.RowCount(tbl)=1 then null else #"Grouped Rows1"[Avg]{0}, #"Grouped Rows" = Table.Group(#"Changed Type", {"Equipment", "CommissionedDate"}, {{"Rows", each _, type table [BreakdownDate=nullable date]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "MTBF", each process([Rows]),type duration), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}) in #"Removed Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
