View excelbi-pq133.m
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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), |
View excel-gaps-islands.txt
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
=LET( | |
test_data, { | |
45232, 1; | |
45233, 1; | |
45234, 2; | |
45235, 2; | |
45236, 1; | |
45237, 1; | |
45238, 2; | |
45239, 2; |
View ColorCellsFromHex.vb
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
View adventureworksdw2019-vSales.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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 |
View excel-lambda-PERCENTOFFN.txt
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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) |
View excel-lambda-ISLAMBDA.txt
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
ISLAMBDA = LAMBDA(x, AND(VALUETOTEXT(x)="λ", TYPE(x)=128))) |
View excel-lambda-BMAPλ.txt
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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, |
View python-in-excel-gpt4-custom-instructions.txt
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
View excel-python-integrate-image-function.py
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
""" |
NewerOlder