ExcelChallenge1040

ExcelChallenge1040.pq

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Sequence", type text}, {"Target", Int64.Type}}
  ), 
  AddedNestedList = Table.AddColumn(
    #"Changed Type", 
    "List1", 
    each 
      let
        tgt = [Target]
      in
        List.Transform(
          Text.Split([Sequence], ", "), 
          each if Number.From(_) = tgt then true else false
        )
  ), 
  AddIndex = Table.TransformColumns(
    AddedNestedList, 
    {"List1", each List.Zip({{0 .. List.Count(_) - 1}, _})}
  ), 
  SelectTargets = Table.TransformColumns(AddIndex, {"List1", each List.Select(_, each _{1} = true)}), 
  SelectIndexes = Table.TransformColumns(
    SelectTargets, 
    {"List1", each List.Transform(_, each _{0})}
  ), 
  StaggerLists = Table.TransformColumns(
    SelectIndexes, 
    {"List1", each List.Zip({_, List.Combine({List.Skip(_, 1), {List.Last(_)}})})}
  ), 
  #"Duplicated Column" = Table.DuplicateColumn(StaggerLists, "List1", "List2"), 
  GetMaxGap = Table.TransformColumns(
    #"Duplicated Column", 
    {"List1", each List.Max(List.Transform(_, each _{1} - _{0}))}
  ), 
  SubtractElements = Table.AddColumn(
    GetMaxGap, 
    "List3", 
    each 
      let
        max = [List1]
      in
        List.Select([List2], each _{1} - _{0} = max){0}{1}
  ), 
  #"Subtracted from Column" = Table.TransformColumns(
    SubtractElements, 
    {{"List1", each _ - 1, type number}}
  ), 
  #"Added to Column" = Table.TransformColumns(
    #"Subtracted from Column", 
    {{"List3", each _ + 1, type number}}
  ), 
  #"Renamed Columns" = Table.RenameColumns(
    #"Added to Column", 
    {{"List1", "Largest Gap"}, {"List3", "Anomaly Position"}}
  ), 
  #"Removed Other Columns" = Table.SelectColumns(
    #"Renamed Columns", 
    {"Sequence", "Target", "Largest Gap", "Anomaly Position"}
  )
in
  #"Removed Other Columns"