Skip to content

Instantly share code, notes, and snippets.

View ncalm's full-sized avatar
hitting computer with hammer

Owen Price ncalm

hitting computer with hammer
View GitHub Profile
ncalm / excelbi-pq133.m
Created November 25, 2023 18:47
Solution to Excel BI challenge PQ133
View excelbi-pq133.m
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Prep the data for calculations later
// In order to merge date and time, convert them to the appropriate types
Retyped = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
// Merge the date and time into a single column
DateTimed = Table.AddColumn(Retyped, "DateTime", each [Date] & [Time], type datetime),
ncalm / excel-gaps-islands.txt
Created November 24, 2023 21:12
Example use of SCAN with GROUPBY to solve gaps and islands problem in Excel
View excel-gaps-islands.txt
test_data, {
45232, 1;
45233, 1;
45234, 2;
45235, 2;
45236, 1;
45237, 1;
45238, 2;
45239, 2;
ncalm / ColorCellsFromHex.vb
Created November 23, 2023 17:08
Code for dot-matrix Turkey Excel pixel-art
View ColorCellsFromHex.vb
Sub ColorCellsFromHex()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("test") ' Change to your sheet's name
Dim i As Integer, j As Integer
Dim hexColor As String
For i = 1 To 50 ' Assuming 50 rows
For j = 1 To 50 ' Assuming 50 columns
hexColor = ws.Cells(i, j).Value ' Get the hex color code from the cell
ncalm / adventureworksdw2019-vSales.sql
Created November 22, 2023 19:05
SQL view and Power Query for replicating GROUPBY testing workbook
View adventureworksdw2019-vSales.sql
/* Install Adventure Works DW 2019 from this link:
View below is used by Power Query in GROUPBY demonstrations
USE [AdventureWorksDW2019]
/****** Object: View [dbo].[vSales] Script Date: 11/22/2023 12:02:03 PM ******/
ncalm / excel-lambda-PERCENTOFFN.txt
Created November 22, 2023 18:58
This Excel LAMBDA gist replicates PERCENTOF functionality for other aggregates
View excel-lambda-PERCENTOFFN.txt
Replicating PERCENTOF for non-SUM aggregates
For percent of max:
In GROUPBY function argument:
Note: At this time, this doesn't work:
ncalm / excel-lambda-ISLAMBDA.txt
Last active October 4, 2023 17:02
This Excel LAMBDA function tests if its argument is a
View excel-lambda-ISLAMBDA.txt
ncalm / excel-lambda-BMAPλ.txt
Created October 3, 2023 12:04
Recursive bisection LAMBDA function for Excel, by Peter Bartholemew
View excel-lambda-BMAPλ.txt
Credit: Peter Bartholemew
Recursively bisects an array and applies a function to the leaf nodes.
View excel-lambda-strip.txt
// trip leading chars
lstrip = LAMBDA(text, char,
LAMBDA(t, IF(LEFT(t)=char, lstrip(MID(t,2,len(t)),char), t))
// trim trailing chars
rstrip = LAMBDA(text, char,
ncalm / python-in-excel-gpt4-custom-instructions.txt
Last active September 27, 2023 13:50
ChatGPT Plus Custom Instructions for Python in Excel
View python-in-excel-gpt4-custom-instructions.txt
There is a feature of Excel called Python in Excel. It allows you to create a "Python cell" in an Excel spreadsheet. In a Python cell, you can execute Python code.
For each session, these imports are run automatically:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import statsmodels as sm
import seaborn as sns
import excel
ncalm /
Last active September 26, 2023 21:16
Use Python in Excel to integrate an expression and render the LaTeX expression and result to a plot
from sympy import integrate
def integration_image(symbol: str, expr: str):
"""Integrates expr with respect to symbol
integration_image(symbol='x', expr='exp(x)*sin(x) + exp(x)*cos(x)')
Dependencies: 'from sympy import integrate' must be run before use