تحليل البلاغات

let    Source   = Csv.Document(File.Contents(Path), [Encoding = 65001]),    Headers  = Table.PromoteHeaders(Source),    Typed    = Table.TransformColumnTypes(Headers, {{"ReportedAt", type datetime}}, "en-GB"),    // The same station is typed "353A", "A353" and "A 353".    // One key: digits first, then letters, no separators.    StationKey = Table.AddColumn(Typed, "StationKey", each        let            raw     = Text.Upper(Text.Remove([Station] ?? "", {" ", "-", "/"})),            digits  = Text.Select(raw, {"0".."9"}),            letters = Text.Remove(raw, {"0".."9"})        in            digits & letters, type text)in    StationKey

def group_faults(reports, radius_m=150, gap_h=48):    """Reports close in space AND time describe one fault."""    sites = []    for r in sorted(reports, key=lambda r: r.time):        near = [s for s in sites if any(meters(r, x) <= radius_m for x in s)]        merged = [r] + [x for s in near for x in s]        sites = [s for s in sites if s not in near] + [merged]    faults = []    for site in sites:        site.sort(key=lambda r: r.time)        wave = [site[0]]        for r in site[1:]:            if hours(r.time - wave[-1].time) > gap_h:                faults.append(wave)                wave = []            wave.append(r)        faults.append(wave)    return faults

Faults = DISTINCTCOUNT ( Reports[FaultKey] )Reports per Fault = DIVIDE ( [Reports], [Faults] )// A site is chronic when faults keep returning, not when one fault was loud.Chronic Sites =COUNTROWS (    FILTER (        VALUES ( Sites[SiteKey] ),        CALCULATE ( [Faults], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -180, DAY ) ) >= 3    ))