cbaragao

(proportion as number, z as number, n as number, optional pu as number)=>

let
  // figure out the plus/minus range
  range = ( 
            z * Number.Sqrt(
                (pu * (1-pu))
                    /
                    n
                )

cbaragao

let
  fnZTable = (test_type as text, value as number) =>
    let

      // define z table - taken from https://www.math.arizona.edu/~rsims/ma464/standardnormaltable.pdf
      standard = Table.FromRecords(
        {
          [z = - 3.99, area_left = 0.00003, area_right = 0.99997, position = "below mean"],
          [z = - 3.98, area_left = 0.00003, area_right = 0.99997, position = "below mean"],
          [z = - 3.97, area_left = 0.00004, area_right = 0.99996, position = "below mean"],

cbaragao

(x as list, y as list)=>

let
    sum_x = List.Sum(x),
    sum_y = List.Sum(y),
    sum_x_sq = List.Accumulate(x, 0, (state, current)=> state + Number.Power(current, 2)),
    sum_y_sq = List.Accumulate(y, 0, (state, current)=> state + Number.Power(current,2)),
    sum_xy = 
        let   
            l = List.Zip({x, y}),

cbaragao

(val as number, col as list) =>

let 
    l = List.Sort(col),
    count = List.Count(l),
    q1 = count * 0.25,
    q2 = count * 0.5,
    q3 = count * 0.75,
    fnRunCheck = (check as number)=>
        let 

cbaragao

(val  as number,col as list) =>

let 
    stdev = List.StandardDeviation(col),
    mean = List.Average(col),
    z = (val - mean)/stdev
in    
    z

cbaragao

/*
This function takes a table and renames snake case column names by 
replacing the underscore with a space
*/



(tbl as table)=>

let 

cbaragao

(x as list, y as list) =>
  let
    // Get the range of the x series/column by subtracting max from min
    range_x = List.Max(x) - List.Min(x), 
    // Get the range of the y series/column by subtracting max from min
    range_y = List.Max(y) - List.Min(y), 
    // Get max index of one of the columns for List.Generate
    max = List.Count(x) - 1, 
    // The equation asks for running differences (think lag in SQL) divided by the range for x and y
    acc = List.Generate(

cbaragao

let fnSwitchMethod = 

 (string as text, switches as list, default as any, optional method as number) =>
 
 let 
    check_method = if method <> null then method else 0, 
    case = if check_method = 0 then 
              try List.Select(switches, each _{0} = string){0}{1} otherwise default
            else if check_method= 1 then 
              try List.Select(switches, each Text.Contains(string,_{0})){0}{1} otherwise default

cbaragao

(tbl as table, col as text, return_col as text) =>
let
    Script= "import spacy #(lf)nlp = spacy.load('en_core_web_md') " &
    "#(lf)#(lf)nlp.max_length = 2000000" &
    "#(lf)#(lf)def get_locations(text):" & 
    "#(lf)    doc = nlp(text)" & 
    "#(lf)    l =  [ent.text for ent in doc.ents if ent.label_ == ""GPE""]" & 
    "#(lf)    return '|'.join(l)#(lf)#(lf)df['" & return_col &"'] = df['" & col &"'].apply(get_locations)",
    Run_Python = Python.Execute(Script,[df=tbl]),
    df = Run_Python{[Name="df"]}[Value]

cbaragao

(tbl as table, pattern as text, base_col as text, new_col as text)=>


let
    Script = "import re#(lf)import pandas as pd#(lf)#(lf)pattern = r'" & pattern & 
                "'#(lf)dataset['" & new_col & "'] = dataset['" 
& base_col & "'].str.extract(pattern)",
    Run_Python = Python.Execute(Script,[dataset=tbl]),
    return = Run_Python{[Name="dataset"]}[Value]
in