ncalm

https://www.linkedin.com/posts/crispo-mwangi-6ab49453_excel-exceltips-crispexcel-activity-7147058784119554048-Am6l?utm_source=share&utm_medium=member_desktop

ncalm

PIVOTEVERYNROWS = LAMBDA(attribute, value, first_attribute, 
    LET(
        unique_attributes, UNIQUE(attribute),
        attribute_ids, SEQUENCE(ROWS(unique_attributes)),
        col_id, XLOOKUP(attribute, unique_attributes, attribute_ids),
        shift, --(attribute = first_attribute),
        row_id, SCAN(, shift, SUM),
        pivoted, PIVOTBY(row_id, HSTACK(col_id, attribute), value, SINGLE,0,0,,0),
        DROP(pivoted, 1, 1)
    )

ncalm

DROP PROCEDURE IF EXISTS change_table_description;
GO

CREATE PROCEDURE change_table_description (
    @table_schema nvarchar(128),
    @table_name nvarchar(128),
    @description nvarchar(255)
)
AS
BEGIN

ncalm

CONEVOLUME = LAMBDA(radius, height, PI() / 3 * radius^2 * height);

ncalm

--- Day 3: Gear Ratios ---
You and the Elf eventually reach a gondola lift station; he says the gondola lift will take you up to the water source, but this is as far as he can bring you. You go inside.

It doesn't take long to find the gondolas, but there seems to be a problem: they're not moving.

"Aaah!"

You turn around to see a slightly-greasy Elf with a wrench and a look of surprise. "Sorry, I wasn't expecting anyone! The gondola lift isn't working right now; it'll still be a while before I can fix it." You offer to help.

The engineer explains that an engine part seems to be missing from the engine, but nobody can figure out which one. If you can add up all the part numbers in the engine schematic, it should be easy to work out which part is missing.

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)