I have generated a complex transformation in Power BI to generate a glide path from the following snippet of extracted data as shown below:
Note: This snippet of data isn't enough to produce the entire glide path shown.
The aim is to measure the status of profiles over time to December 15th 2023.
The status of each profile can be, Assigned, Started, PeerReviewStarted, Submitted, or Approved. The date for when each stage per profile commences is captured. Profiles which have no date as considered as 'Pending' though for now this is not considered.
Basically, I would like to provide the M code and Input data and see if someone better at transforming data can make it much more efficient as it takes a long time to load and really is just confusing.
The request:
Intuitively I feel this can be really improved as the M code is a bit of a hot mess.
Feel free to tweak or reinvent entirely.
The main request is to generate a glide path for each attribute of the profile on the same chart. In additon I have the Target, and attribute rate projections as linear projections (there may be a better way to predict this sort of data but linear was okay). To be honest the main request is just the 'glidepaths' but anything else is a bonus.
SAMPLE DATA (Please see onedrive file for all data):
| ProfileID | Assigned | Started | PeerReviewStarted | Submitted | Approved |
|---|---|---|---|---|---|
| INGPR0467 | 10/08/2023 | null | null | null | null |
| INGPR0007 | 22/08/2023 | null | null | null | null |
| INGPR0604 | null | null | null | null | null |
| INGPR0448 | 13/09/2023 | 13/09/2023 | 02/11/2023 | 12/10/2023 | null |
| INGPR0613 | 27/09/2023 | 27/09/2023 | 02/11/2023 | null | null |
| INGPR0538 | 07/11/2023 | 07/11/2023 | 10/11/2023 | null | null |
| INGPR0634 | null | null | null | null | null |
| INGPR0625 | 10/08/2023 | null | null | null | null |
| INGPR0425 | null | null | null | null | null |
| INGPR0442 | null | null | null | null | null |
| INGPR0328 | null | null | null | null | null |
| INGPR0513 | null | null | null | null | null |
| INGPR0441 | null | null | null | null | null |
| INGPR0499 | 26/07/2023 | 27/07/2023 | null | null | null |
| INGPR0044 | 11/07/2023 | null | null | null | null |
| INGPR0478 | null | null | null | null | null |
| INGPR0615 | null | null | null | null | null |
| INGPR0096 | 11/07/2023 | 27/07/2023 | null | null | null |
| INGPR0119 | 23/08/2023 | 04/10/2023 | null | null | null |
| INGPR0097 | 11/07/2023 | 11/07/2023 | 02/08/2023 | 12/10/2023 | 17/11/2023 |
| INGPR0361 | null | null | null | null | null |
| INGPR0145 | null | null | null | null | null |
| INGPR0385 | null | null | null | null | null |
| INGPR0338 | null | null | null | null | null |
| INGPR0452 | 22/08/2023 | null | null | null | null |
| INGPR0440 | 22/08/2023 | null | null | null | null |
| INGPR0564 | null | null | null | null | null |
| INGPR0496 | 31/07/2023 | 31/07/2023 | 31/07/2023 | 08/08/2023 | 17/08/2023 |
| INGPR0172 | null | null | null | null | null |
| INGPR0395 | null | null | null | null | null |
M CODE VERSION 2 COMBINED A FEW STEPS BUT NOT NECESSARILY MORE EFFICIENT:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVxLjiM3DL1LrwdokvqVThBkEwTZDuYGg+xy/9iy3U2KkqWn1UDTVklFUeTjI1k/f378+dcff/9DMZePHx9Mn3R9Ckm4Df797/fv8T+/frymEd2niYDTYrzuq4VPqq9pZkDyyfz1l9uAzNO/H5T5/p9S1FwzMA8a7yWF+16oqB+awW3t1SNyiG9f3E+QdCLtGAVbJ0YGJ9R6F2H+vIlAybO82aVShngXw01Yb38/OMOEbZJq7tfZ3yRze8OgRE+xV7D5yqVf2QxIXwSjt/ytUt+PCxf45qHpKnKeSc7uJx1NawcTtETmg9ujlazK10C9bUXFw9JfK06T2+vP9mqTL31oeiA0HeSvgdIzknf2Z/oKuVnGCE5L9P6Xg3XqyfYSXSfTcsjgFU/1qRVleEspLI0yCag9ws72KbVs2jC+DmrNTNiaKaC2LxT0ra7+rYjf32ttB8DVqHgZ7q+W0mjytzPPEzutHnE1Ax20azB+ouoDneAPbdC88PQTzONuCrp4XBKHlrjOrMrMxNWP3gNFPdDG7/Y8taORlYopv9V5qssdEQpKEtFbc2rQ1+SYOfbHbM5lsT4IijiwM0bbq+XYDkxAUCTNA/MC1njBCHhhOebzd+OL3t52iUuLyc3rmkdsvWeJvbLfPL0yFcZZpJWzFthXpDMAH5ze3gQ2gxV62zeb7fFjLKgmP7ZtrIfWTCprF9csrNpO54w3PHN6GB3pfGvd0rmQRmZOSa1oAKdjNwUF1ePE6R+FMTCd3nBxqNdi22iDgoWTuG3p5EZwoKccsWnNGJu4RbTPlTRzwEN/kiIIgDjUk23HgAKt5smtdSq7YV+k2ivs1poJtSotgIaP/uaC+ysRUIeaX8fwfdi8GzmlknqrZm+DUajLcjWjyCl9HIRA1EKT0COjXU92FHZJs2T4VnM7sC3oN4C4oOqH6E7E3GqZ2V79CBBWxOT9c3/3FirJzTLPOUIuy21Tfc6ahPSLS0/9K1DYnRzK1dtLazzXSD9V5wcAloti/+ZbqnKBNAJf9WMdUHrZCm1YGH+eyRmmfStFxZFDW2vWo2n8gP3oAQg5eXLchUYM82UPrQrguwmXI2OJGi5uOQnSRtwAKzK0vwZW97jcASuhF7oZw05Z5wpifkVlmMCEnSnZ55wZ5Hxvcvvog4wJ6uyM/91EOYRMwaH1u1H6ftyaIkiX9wZae3jtDRKDoU64QFoiXKCcQ3MQoeP/LUdJSujDgSaHwCRSEnTDxQcqJk7WAy6roDmwj27C9Ak6LFQDLc7a724foYcICk/qC11MIvHZ0vr9Y7/hvZWPgonAqRfwlqdFyS7xHp2n8fXsDeGbxL39tYMNToFRVEy5116Z8csDx5mOzvAwW05HbjoQCAfSI+lylj4LjReCb0OC2WNGJ0QQ0ybUlnBGV0Az3rGitC6DE9Lj2uf3WNNNyw3ZwPifyDHqK6pbmZf88q5Y8ND8K07EF+/lF0vryQ3BzhDS9PQKnUg1EZhl5Rr6Q7eDGUjVsQD3Js2Y0bAOdIWPQDU12tVEByZbuMEPcL1OlJ4aiz8X2/tDyi2sn21ual7TUX2WNCHZkqhpamR23dCossIc8LMaDIyVfcmaLxWbWtT0ChoxnyroFUuv/Bq2DprZyXBpGFpKxINCgoX4NOPp9JDzNnPTeA34ytBDrWDmRkDR0+WiexNKvD+GZj9Xvx/oIYpRGjwzMgzbm0zijPyWP8ouJtzaKu3gAS/JZlhhSRI9pykoUvdq1tKwZg02EA0iTJliml0TfblQbgQFrFSO0t0pHXGn0gIXPJRAk/jhQZSZi1uwsFNa3gwxuwW00xnNqEptkPwsuSRX7i2DrYy+3tJ2ettgAJIj6I9Se09b/GQGecZaDLOO8VUIo85/P7dwOY506xUuNKwTNO1djkwwPepETCVYnimUqQZVAw0d4BIxRybsVyURe7u9n8tu4NV4ERPCGEJEXGuAFuETnU6SfjsVrOFZDwkeXnj5T5BlOiIYszjSaFpv4PWZXY5VtnOs0gplTPoBrUSKBXQZHMqJkOTrSLR52tRnkWf2ZNKTMk3Eaa0+K1cT0LtJQgvS5Mw+kbviWw7fwxibpDBEt8lljDJjko6S7Bmt+WZ26bMtIV0uFDElhVNuV/NXIFBkuXqTZQDsHZas1vSm1Q5mtlxjarRNJrqSo1V6W2MmUEihRSqHDipHOMCgEy3ljEaYr+I10DQW8H1iOio5kQZI4e2FR9XIGVMdI2g/uYCKK2cRVkYBWSxoFILCeYpHhBxnUH3E1w/v6QF4HWJ5tQqCnXdoIrscdfhJ4wysbc6zQG9yYr6bxQPi6foEhj6hOKuM1pDQ5arOzGDDs1A54hQJjdsE7QyVC9QbalBt7oQ2qqGk5ZsM08DTrp7JqQbUd9ZX5hA0E6DBo+Rowb2DdvphA5S1lgraTC5oviVeZ7WJMPmQHDG+NS26iHJPDHArZTOBBhJv548EbtyEe71RD8qoCQiXLxTsqrlU2tB0I9UBg0YJdZHs8n72ywvrAIXg5GQ80sn4iGu0r7N+ZO0uRa5e2jSlKC0DOwo+GS6cgV0KGv2H5Pj0Pdvsm9K4w9uLFqyEqp7E+u7J81ufTyxnIvSsYLyQ0ZK7hwDSznXzt+5RiyhdhfH34LJFnMMOMs1OoYYDlSejNWDsi1FVuq/THNPmOnxDQaMHOSuJyINuRtOvvka4csZwhkYX4BmQRm2t6nz9tAAqgDAYJga0CoDDEX0uraPAuA/DkW2QuzGh9TZoLT1faIIZbc8MhDJ21xH3mtrG5mmCnVbJdHQ1U3qiKAOpVCPGBot/+I0u/DBg83pEMQZ2pZV7rsvBVtfFMkb0w5blQEeWS9IREyBoW4r4bOaWuqHF34JSgqE8yzaxMlyGiR/0izcBrX1mlIJOKGtBZxUtOaCfb7tcJ7alZ3h2NYY1EaGgbb9oYVFGz0pQZ0f+SyH7LCV7BoP6r7rR0FWrNbSzR8nqfNRiFP0HAbYOA4062KvbfssWoz0NsRyFl/KI+HEojX7TES2oDagA6PFVPt0HZL9jmKdecPT1wZSe7hf02v5O7BdKcYtTD5J3qHMKqVfG/YRuoiMCMhzm4oKcb5XqWZ8/Kk+qtb/p06+xDbzSi0vXIfOa7Bq4l9Z4YRhb6uS2SDgQSpQICoYju0/Q7YsqwR6/ongSrTkkuJuzojkdmGisroDizbRf/wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProfileID = _t, Assigned = _t, Started = _t, PeerReviewStarted = _t, Submitted = _t, Approved = _t]),
#"Replaced Value1" = Table.ReplaceValue(Source,null,"",Replacer.ReplaceValue,{"Assigned", "Submitted", "PeerReviewStarted", "Approved", "Started"}),
RowCount = Table.RowCount(Source),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"ProfileID"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Value"}, #"All Dates", {"Date"}, "All Dates", JoinKind.RightOuter),
#"Expanded All Dates" = Table.ExpandTableColumn(#"Merged Queries", "All Dates", {"Date"}, {"Date"}),
//StartDate = List.Min(#"Expanded All Dates"[Value]),
//FinalDate = List.Max(#"Expanded All Dates"[Value]),
#"Filtered Rows" = Table.SelectRows(#"Expanded All Dates", each [Date] >= List.Min(#"Expanded All Dates"[Value])),
Custom1 = Table.AddColumn(#"Filtered Rows", "Gradient", each Number.Round(RowCount * (Duration.Days(List.Max(#"Expanded All Dates"[Value]) - [Date]) / Duration.Days(List.Max(#"Expanded All Dates"[Value]) - List.Min(#"Expanded All Dates"[Value]))), 0)),
#"Sorted Rows5" = Table.Sort(Custom1,{{"Gradient", Order.Descending}}),
#"Sorted Rows" = Table.Sort(#"Sorted Rows5",{{"Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Grouped Rows1" = Table.Group(
#"Added Index",
{"Attribute"},
{
{
"Grouped",
each Table.AddIndexColumn(_, "Count", List.Max(#"Added Index"[Gradient]) - 1, -1, Int64.Type)
}
}
),
#"Replaced Value" = Table.ReplaceValue(#"Grouped Rows1",null,"Pending",Replacer.ReplaceValue,{"Attribute"}),
#"Expanded Grouped" = Table.ExpandTableColumn(#"Replaced Value", "Grouped", {"Date", "ProfileID", "Index", "Count", "Gradient"}, {"Date","ProfileID", "Index", "Count", "Gradient"}),
#"Appended Query" = Table.Combine({#"Expanded Grouped", Attribute}),
Custom2 = Table.ReplaceValue(#"Appended Query",null,Date.AddDays(List.Min(#"Expanded All Dates"[Value]), -1),Replacer.ReplaceValue,{"Date"}),
Custom3 = Table.ReplaceValue(#"Custom2",null,RowCount,Replacer.ReplaceValue,{"Index", "Count", "Gradient"}),
#"Pivoted Column" = Table.Pivot(Custom3, List.Distinct(Custom3[Attribute]), "Attribute", "Count"),
#"Sorted Rows4" = Table.Sort(#"Pivoted Column",{{"Date", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows4",{"Pending", "Index"}),
#"Sorted Rows3" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}, {"Assigned", Order.Descending}, {"Started", Order.Descending}, {"PeerReviewStarted", Order.Descending}, {"Submitted", Order.Descending}, {"Approved", Order.Descending}}),
Custom4 = Table.AddColumn( #"Sorted Rows3", "Assigned Rate",
each
(
-(
(List.Max(#"Sorted Rows3"[Assigned]) - List.Min(#"Sorted Rows3"[Assigned]))
/ Duration.Days(Date.From(DateTime.LocalNow()) - List.Min(#"Expanded All Dates"[Value]))
)
* Duration.Days([Date] - Date.AddDays(List.Min(#"Expanded All Dates"[Value]), -1))
)
+ List.Max(#"Sorted Rows3"[Gradient])
),
Custom5 = Table.AddColumn(Custom4, "Started Rate",
each
(
-(
(List.Max(Custom4[Started]) - List.Min(Custom4[Started]))
/ Duration.Days(Date.From(DateTime.LocalNow()) - List.Min(#"Expanded All Dates"[Value]))
)
* Duration.Days([Date] - Date.AddDays(List.Min(#"Expanded All Dates"[Value]), -1))
)
+ List.Max(Custom4[Gradient])
),
Custom7 = Table.AddColumn(Custom5, "PeerReviewStarted Rate",
each
(
-(
(List.Max(Custom5[PeerReviewStarted]) - List.Min(Custom5[PeerReviewStarted]))
/ Duration.Days(Date.From(DateTime.LocalNow()) - List.Min(#"Expanded All Dates"[Value]))
)
* Duration.Days([Date] - Date.AddDays(List.Min(#"Expanded All Dates"[Value]), -1))
)
+ List.Max(Custom5[Gradient])
),
Custom6 = Table.AddColumn(Custom7, "Submitted Rate",
each
(
-(
(List.Max(Custom7[Submitted]) - List.Min(Custom7[Submitted]))
/ Duration.Days(Date.From(DateTime.LocalNow()) - List.Min(#"Expanded All Dates"[Value]))
)
* Duration.Days([Date] - Date.AddDays(List.Min(#"Expanded All Dates"[Value]), -1))
)
+ List.Max(Custom7[Gradient])
),
Custom8 = Table.AddColumn(Custom6, "Approved Rate",
each
(
-(
(List.Max(Custom6[Approved]) - List.Min(Custom6[Approved]))
/ Duration.Days(Date.From(DateTime.LocalNow()) - List.Min(#"Expanded All Dates"[Value]))
)
* Duration.Days([Date] - Date.AddDays(List.Min(#"Expanded All Dates"[Value]), -1))
)
+ List.Max(Custom6[Gradient])
),
#"Changed Type3" = Table.TransformColumnTypes(Custom8, {
{"Assigned", Int64.Type},
{"Started", Int64.Type},
{"PeerReviewStarted", Int64.Type},
{"Submitted", Int64.Type},
{"Approved", Int64.Type},
{"Assigned Rate", Int64.Type},
{"Started Rate", Int64.Type},
{"PeerReviewStarted Rate", Int64.Type},
{"Submitted Rate", Int64.Type},
{"Approved Rate", Int64.Type}
})
in
#"Changed Type3"
ALL DATES M CODE:
let
Source = #date(2023, 1, 1),
EndDate = #date(2023, 12, 15),
Custom1 = List.Dates(Source, Duration.Days(EndDate - Source) + 1, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "QuarterNo", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Quarter", "Quarter", each "Q" & Text.From([QuarterNo])),
#"Inserted Month" = Table.AddColumn(#"Added Custom", "MonthNo", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Text.Start(Date.MonthName([Date]), 3), type text),
// Append a new row with a blank date value
BlankRow = #table(type table[Date=date, Year=number, QuarterNo=number, Quarter=text, MonthNo=number, #"Month Name"=text], {{null, null, null, null, null, null}}),
ResultTable = Table.Combine({#"Inserted Month Name", BlankRow})
in
ResultTable
ATTRIBUTE M CODE:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwuzkzPS01RitWJVgouSSwqgbFLk3IzS2A8x4KCovwyKCcgNbUoKLUsM7UcriEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Attribute"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "ProfileID", each "")
in
#"Added Custom"
Or if you just want to download the Power BI Workbook you can find a made up example here (Max 400 profiles rather than actual 581 as in image).
ONE DRIVE FILE