cbaragao

-- Using DuckDb
-- Example is Lag 1

CREATE TEMP TABLE t1 (
	SERIES DECIMAL(18,2)
);
INSERT INTO t1 VALUES(895);
INSERT INTO t1 VALUES(432);
INSERT INTO t1 VALUES(282);
INSERT INTO t1 VALUES(879);

cbaragao

function Get-RecentFiles {
    param (
        [string]$Path = ".",
        [int]$Days = 1,
        [switch]$Recurse,
        [string]$Extension
    )

    if (-Not (Test-Path $Path)) {
        Write-Error "Path not found: $Path"

cbaragao

Import-Module PSDuckDB
# Start timing
$startTime = Get-Date

# Parameters
$table = "test_table"
$sql = "SELECT * FROM $table"
$output = "Output\QueryResults.csv"

# Create a connection

cbaragao

// Moving Average

#r "Microsoft.VisualBasic"
using Microsoft.VisualBasic;
using System.Text.RegularExpressions;

// Select a measure
var offsetMeasure = Model.SelectMeasure();

// Select an offset column

cbaragao

/*
Benefit: Automatically removes projections from consideration

Make sure Dates[Year] and Dates[Month Name] are on the visual.
Example for a Matrix put these columns in the Rows well in this order
    - Year
    - Month Name
*/

cbaragao

#r "Microsoft.VisualBasic"
using Microsoft.VisualBasic;
using System.Text.RegularExpressions;

// Select a measure
var offsetMeasure = Model.SelectMeasure();

// Select an offset column
string offsetColumn = SelectColumn(Model.Tables["Dates"].Columns.Where(
                                       col => col.Name.Contains("Offset")))

cbaragao

#r "Microsoft.VisualBasic"

using Microsoft.VisualBasic;
using System;
using System.Windows.Forms;

string stringInterval =
    Interaction.InputBox(
        Prompt: "Enter the interval for the moving average:",
        Title: "Interval",

cbaragao

let
    // Define the base API endpoint for the Dataverse environment
    baseUrl = "https://your-org.crm.dynamics.com/api/data/v9.2/",

    // Define the schema name of the environment variable you want to fetch
    schemaName = "schema_name",  // Replace with the actual schema name

    // Fetch the environment variable definition that matches the given schema name
    definitionQuery = Web.Contents(
        baseUrl, 

cbaragao

let
  // Construct the URL to fetch environment variable values
  values_url = Env 
    & "environmentvariablevalues?$select=environmentvariablevalueid,value,_environmentvariabledefinitionid_value", 

  // Retrieve the environment variable values from the OData feed
  values = OData.Feed(values_url, null, [Implementation = "2.0"]), 

  // Construct the URL to fetch environment variable definitions
  definition_url = Env 

cbaragao

(
    // table to pass in
    tbl as table, 
    // name of the date column
    date_column as text, 
    // name of the column you are projecting on
    projection_column as text, 
    // "add", "mul", "None"
    trend as text, 
    // "add", "mul", "None"