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
ncalm / excelbi-pq133.m
Created November 25, 2023 18:47
Solution to Excel BI challenge PQ133
View excelbi-pq133.m
let
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
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
=LET(
test_data, {
45232, 1;
45233, 1;
45234, 2;
45235, 2;
45236, 1;
45237, 1;
45238, 2;
45239, 2;
@ncalm
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
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:
https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2019.bak
View below is used by Power Query in GROUPBY demonstrations
*/
USE [AdventureWorksDW2019]
GO
/****** Object: View [dbo].[vSales] Script Date: 11/22/2023 12:02:03 PM ******/
SET ANSI_NULLS ON
@ncalm
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
Usage:
For percent of max:
=PERCENTOFFN(MAXL)(data_subset,data_all)
In GROUPBY function argument:
PERCENTOFFN(MAXL)
Note: At this time, this doesn't work:
=PERCENTOFFN(MAX)(data_subset,data_all)
@ncalm
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
ISLAMBDA = LAMBDA(x, AND(VALUETOTEXT(x)="λ", TYPE(x)=128)))
@ncalm
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
https://www.linkedin.com/in/peterbartholomew/
https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/214174#profile
---
BMAPλ
Recursively bisects an array and applies a function to the leaf nodes.
View excel-lambda-strip.txt
// trip leading chars
lstrip = LAMBDA(text, char,
BYROW(text,
LAMBDA(t, IF(LEFT(t)=char, lstrip(MID(t,2,len(t)),char), t))
)
);
// trim trailing chars
rstrip = LAMBDA(text, char,
BYROW(text,
@ncalm
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
ncalm / excel-python-integrate-image-function.py
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
View excel-python-integrate-image-function.py
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
"""