Forum Discussion
Last available date value vs current date value increase or decrease
That is also correct there is only 1 unique batch per product always.
It's a pharma company who produces medicine and for each batch of drug substance there is a bunch of products created. The number of products per batch is captured in another column called "quantity".
This analysis is to help detect if the potent active medicine is at the right intensity level. It must fall within certain threshold and calculating if there is an increase or decrease in the intensity level between each batch is important because 5+ batches with consecutive same change in intensity is no longer an incident, but a pattern that need to be addressed.
this sample data is just to show that for product B there is no pattern detected, but there is for product A.
does it make sense? thanks for your reply
I added some more sample data to cover more scenarios. It's still not a lot. Anyway - here is the Power Query. Any value of 5 and above or -5 and below indicates the streak length.
let
fx = (tb) =>
let
#"Added Index" = Table.AddIndexColumn(tb, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(
#"Added Index",
"Flag",
each
if [Index] < 5 then
0
else
List.Accumulate(
{[Index] - 4 .. [Index]},
0,
(state, current) =>
if #"Added Index"[value]{current} > #"Added Index"[value]{current - 1} then
state + 1
else if #"Added Index"[value]{current} < #"Added Index"[value]{current - 1} then
state - 1
else
0
)
)
in
#"Added Custom",
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"fc/NDYAgDIDRXThjAkX8OVrUJQj7ryE1VoJUEumBF8zXGNWm9H2sycPYIX9gwKmko0I2S8NVFtggD/CV7Wzu+ScUO9hGMqjsZPNkvjK6R24ROpF3EDrJVjkTeQUhE98V2szyrs0MnczQyQz/mUSLXEk0y5FE06cxXQ==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [batch = _t, Product = _t, value = _t, #"purification date" = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"purification date", type date}}, "en-GB"),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type", {{"value", Int64.Type}}),
#"Grouped Rows" = Table.Group(
#"Changed Type1",
{"Product"},
{
{
"Rows",
each _,
type table [
batch = nullable text,
Product = nullable text,
value = nullable number,
purification date = nullable date
]
}
}
),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Checked", each fx([Rows])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"Checked"}),
#"Expanded Checked" = Table.ExpandTableColumn(
#"Removed Other Columns",
"Checked",
{"batch", "Product", "value", "purification date", "Index", "Flag"},
{"batch", "Product", "value", "purification date", "Index", "Flag"}
)
in
#"Expanded Checked"