ncalm

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

=LET(
    test_data, {
        45232, 1;
        45233, 1;
        45234, 2;
        45235, 2;
        45236, 1;
        45237, 1;
        45238, 2;
        45239, 2;

ncalm

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

/* 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

/*
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

ISLAMBDA = LAMBDA(x, AND(VALUETOTEXT(x)="λ", TYPE(x)=128)))

ncalm

/*
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. 

ncalm

// 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

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

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
    """