Polybius Cipher Encryption

Extract the valid times from the given strings. Time appears as HH:MM HH: This will either be 1 digit or 2 digits (will range between 0 to 23) MM: This will always be two digits (will range between 00 to 59) Note – If a portion of string is already used, then nothing from that portion can be reused. Ex- 13:518:14 = Since 13:51 has been used, next answer would be 8:14 not both 18:14 and 8:14

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 155
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Extract Valid Time Entries with Power Query

Power Query solution 1 for Extract Valid Time Entries, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  C = each try Time.FromText(_, [Format = "H:mm", Culture = "en-US"]) otherwise null, 
  P = (i, o) =>
    let
      M = (s, c) => Text.Middle(i, s, c), 
      L = Text.Length, 
      p = Text.PositionOf(i, ":"), 
      N = each p - _ >= 0 and List.Contains({"0" .. "9"}, Text.At(i, p - _)), 
      s = (if N(2) then M(p - 2, 2) else if N(1) then M(p - 1, 1) else "")
        & (
          let
            r = M(p, 3)
          in
            if L(r) = 3 then r else ""
        ), 
      d = if o = "" then o else ", "
    in
      if p = - 1 then o else @P(M(p + 3, L(i)), o & (if C(s) <> null then d & s else "")), 
  S = Table.AddColumn(Source, "Expected Answer", each P([String], ""))
in
  S

Power Query solution 2 for Extract Valid Time Entries, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  Sol = Table.AddColumn(
    Origen, 
    "Answer", 
    each 
      let
        a = Text.SplitAny([String], Text.Combine({"A" .. "Z", "a" .. "z", "_", "$"})), 
        b = List.Select(a, each _ <> "" and Text.Contains(_, ":")), 
        c = List.Combine(
          List.Transform(b, each try Splitter.SplitTextByRepeatedLengths(5)(_) otherwise _)
        ), 
        d = List.Select(
          c, 
          each try Value.Is(Time.From(_), Time.Type) and Text.Length(_) > 3 otherwise false
        )
      in
        Text.Combine(d, ", ")
  )
in
  Sol

Power Query solution 3 for Extract Valid Time Entries, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  res = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each 
      let
        a = List.Zip({{"a" .. "z", "A" .. "Z"}, List.Repeat({" "}, 52)}), 
        b = Text.Combine(List.ReplaceMatchingItems(Text.ToList([String]), a)), 
        c = Text.SplitAny(b, ":_$ "), 
        d = List.Select(
          List.TransformMany(
            c, 
            each Text.Split(Text.Middle(_, 0, 2) & " " & Text.Middle(_, 2, 2), " "), 
            (x, y) => y
          ), 
          each _ <> " " and _ <> ""
        ), 
        e = List.Select(
          List.Split(d, 2), 
          (x) => Number.From(x{0}) <= 23 and Number.From(x{1}) <= 59 and Text.Length(x{1}) = 2
        ), 
        f = Text.Combine(List.Transform(e, each Text.Combine(_, ":")), ", ")
      in
        f
  )
in
  res

Power Query solution 4 for Extract Valid Time Entries, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData155"]}[Content], 
  Result = Table.AddColumn(
    Source, 
    "Hours", 
    each 
      let
        l = List.Generate(
          () => [s = Text.Select([String], {"0" .. "9", ":"}) & " ", r = {}], 
          each Text.Length([s]) > 0, 
          each 
            let
              h  = Text.End(Text.BeforeDelimiter([s], ":"), 2), 
              m  = Text.Start(Text.AfterDelimiter([s], ":"), 2), 
              hm = if (Text.Contains([s], ":")) then h & ":" & m else [s]
            in
              [s = Text.AfterDelimiter([s], hm), r = [r] & {hm}]
        ), 
        s = List.Select(List.Last(l)[r], each try Time.From(_) is time otherwise false)
      in
        Text.Combine(s, ", ")
  )
in
  Result

Power Query solution 5 for Extract Valid Time Entries, proposed by Arden Nguyen, CPA:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T = List.Transform, 
  col = Table.AddColumn(
    Source, 
    "Custom", 
    each 
      let
        a = List.RemoveItems(
          Splitter.SplitTextByAnyDelimiter({"a" .. "z", "A" .. "Z", "$", "_"})([String]), 
          {""}
        ), 
        b = Text.Combine(
          List.RemoveItems(
            T(
              a, 
              (x) =>
                [
                  _a = Text.PositionOf(x, ":", Occurrence.All), 
                  _b = T(_a, each _ + 3), 
                  _c = Splitter.SplitTextByPositions({0} & _b)(x), 
                  _d = T(
                    _c, 
                    each 
                      if Text.Length(Text.AfterDelimiter(_, ":")) = 2 then
                        try Time.ToText(Time.From(_), [Format = "hh:mm"]) otherwise null
                      else
                        null
                  ), 
                  _e = Text.Combine(_d, ", ")
                ][_e]
            ), 
            {""}
          ), 
          ", "
        )
      in
        b
  )
in
  col

Solving the challenge of Extract Valid Time Entries with Excel

Excel solution 1 for Extract Valid Time Entries, proposed by Bo Rydobon 🇹🇭:

=MAP(A2:A11&"a",LAMBDA(a,LET(m,MID(a,SEQUENCE(LEN(a)),1),c,CONCAT(m&DROP(REPT("a",VSTACK(0,0,m)=":"),-2)),w,TEXTSPLIT(c,,TEXTSPLIT(c,":",SEQUENCE(10)-1,1)),
TEXTJOIN(", ",,REPT(w,(w=TEXT(w,"h:mm"))+(w=TEXT(w,"hh:mm"))>0)))))

Excel solution 2 for Extract Valid Time Entries, proposed by محمد حلمي:

=MAP(A2:A10,LAMBDA(a,LET(v,UNIQUE(TOCOL(FIND(":",
a,SEQUENCE(20)),2)),r,IF(1+ISNUMBER(-MID(a,v-2,1))
-IFERROR(VSTACK(2,1+(DROP(v,1)-DROP(v,-1)>4)),2),
1,2),x,MID(a,v-r,r),u,MID(a,v+1,2),TEXTJOIN(", ",,IFERROR(
IFS(--x<24,x)&":"&IFS((LEN(u)=2)*(--u<60),u),"")))))

Excel solution 3 for Extract Valid Time Entries, proposed by Ziad A.:

=ARRAYFORMULA(TRIM(REGEXREPLACE(A2:A10,"((?:[01]?[0-9]|2[0-3]):[0-5][0-9])|.","$1 ")))

Solving the challenge of Extract Valid Time Entries with Python in Excel

Python in Excel solution 1 for Extract Valid Time Entries, proposed by Abdallah Ally:

import pandas as pd
import re
file_path = r'C:UsersaallyDownloadsPower_Query_Challenge_155.xlsx'
df = pd.read_excel(file_path, usecols=[0, 3])
# Create a function to extract valid times from a string
def extract_valid_times(col):
 valid_times = [x for x in re.findall(r'd{1,2}:d{2}', col) 
 if int(x.split(':')[0]) < 24 and int(x.split(':')[1]) < 60]
 return ', '.join(valid_times)
# Create a new column containing valid times extracted from a string 
df['My Answer'] = df['String'].apply(extract_valid_times)
# Replace empty string values with nan values
df['My Answer'] = df['My Answer'].replace('', float('nan'))
print(df)

Python in Excel solution 2 for Extract Valid Time Entries, proposed by JvdV -:

import re
[', '.join(re.sub(r'2[4-9]|[3-9]d:|(dd?:[0-5]d)|.',r'1 ',s).split())for s in xl("A2:A10")[0]] 
https://regex101.com/r/E4LOsm/1

Solving the challenge of Extract Valid Time Entries with R

R solution 1 for Extract Valid Time Entries, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_155.xlsx", range = "A1:A10")
test = read_excel("Power Query/PQ_Challenge_155.xlsx", range = "D1:D10")
extract = function(string) {
 subs = string %>% 
 str_extract_all("\d{1,2}\:\d{2}") %>%
 unlist()
 subs = purrr::map_chr(subs, function(x) {
 ifelse(
 as.numeric(strsplit(x, ":")[[1]][1]) %in% 0:23 & as.numeric(strsplit(x, ":")[[1]][2]) %in% 0:59,
 x,
 NA_character_
 )
 }) %>%
 na.omit() %>%
 str_c(collapse = ", ")
 return(subs)
}
result = input %>% 
 mutate(extracted = map_chr(String, extract),
 extracted = ifelse(extracted == "", NA_character_, extracted))

&&&

Solving the challenge of Extract Valid Time Entries with Power Query

Solving the challenge of Extract Valid Time Entries with Excel

Solving the challenge of Extract Valid Time Entries with Python in Excel

Solving the challenge of Extract Valid Time Entries with R

Leave a Reply Cancel reply