How to use or transform product availability logs?

Question

I have product availability logs which contain date, new / old value (as below)

.

I would like to use them in the analysis, checking whether the product was available or not on a given day.

I was thinking about transforming the log table into a table containing each day and a value of 0/1 based on the logs. How can I do that easily?

Can it be done with measures in DAX?

I can use Power BI, Power Query, SQL or r / python if it would be more convenient

Thanks for any suggestions

Based on you table example. How do you determine that a product is available? When `New_value` = 1 ? — Jérémie L, Jun 23 '22 at 07:31
Yes. If new value = 1 then from 'Updated' date product is available. — kamil, Jun 23 '22 at 08:11
In Power Query you can use `List.Generate` to create lists of the individual dates within each date range; then add those lists as a new column and expand (to rows). — Ron Rosenfeld, Jun 23 '22 at 11:14

score 1 · Accepted Answer · answered Jul 01 '22 at 07:42

the solution works, thank you @David.

Nevertheless, I have used R language because it took a long time in PQ (tens of millions of rows to generate).

Code in R

    df <- df %>%
  rowwise() %>%
  transmute(col1,
            col2,
            date = list(seq(date1, date2, by = "day")),
  ) %>%

score 0 · Answer 2 · answered Jun 23 '22 at 18:39

Try this. The new custom column combined with New_value is your stock status at any given date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIAYWN9AxN9IwMjQ6VYnWiwEFjKEkUYptrQAKtqQyPsqrGbbWiGVbUR0EozqHAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Old_value = _t, New_value = _t, Updated = _t]),
    Table = Table.TransformColumnTypes(Source,{{"Old_value", Int64.Type}, {"New_value", Int64.Type}, {"Updated", type date}}),
    #"Added Custom" = Table.AddColumn(Table, "Custom", each let 
        initList = List.Sort(Table[Updated], Order.Ascending),
        dateChange = [Updated],
        minDate = List.Min(List.Select(initList, each _ > dateChange)),
        dateList = try List.Dates(dateChange,Duration.Days(minDate-dateChange),#duration(1, 0, 0, 0)) otherwise {dateChange}
        in dateList),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

How to use or transform product availability logs?

2 Answers2