Forum Discussion

Sebatian's avatar
Sebatian
New Member
2 years ago
Solved

Power Bi,Power Query - Variable for: errors/production time

Hello I have a general question:   Initial situation: Excel File 1 - Production Time: The viewing period spans one month. The period is segmented into various intervals. These intervals are recor...
  • AlienSx's avatar
    2 years ago

    Hello, Sebatian 

    let
        events_table = your_events_table,
        interval_table = your_intervals_table,
        event = #table(
            {"type", "value"}, 
            {
                {"event", events_table[Date_time_event]}, 
                {"start", interval_table[IntervalStart]},
                {"end", interval_table[IntervalEnd]}
            }
        ),
        expand = Table.ExpandListColumn(event, "value"),
        sort = Table.Sort(expand,{{"value", Order.Ascending}}),
        g = Table.Group(
            sort, "type", 
            {{"events", (x) => List.Count(List.Select(x[type], (w) => w = "event"))}, {"date", (x) => Date.From(x{0}[value])}}, 
            GroupKind.Local, 
            (s, c) => Number.From(List.Contains({"start", "end"}, c))),
        filter_start = Table.SelectRows(g, each ([type] = "start")),
        g2 = Table.Group(filter_start, {"date"}, {{"events", each List.Sum([events]), type number}})
    in
        g2