m-dekorte’s gists

m-dekorte / colRenamesAndTypes.pq

Created February 28, 2025 14:28

colRenamesAndTypes Example | manage renaming columns and setting column data types

m-dekorte / getAllSubstrings.pq

Last active June 11, 2025 10:01

getAllSubstrings M function | Generate all possible contiguous substring combinations from a string.

	let
	/* This was an exercise just for fun. Who knows, it might have a real-world application... */
	/* Generate every possible substring combination from a string */
	getAllSubstrings = (s as text, optional makeDistinct as logical, optional ignoreCase as logical) as list =>
	let
	c = if ignoreCase ?? false then Comparer.OrdinalIgnoreCase else Comparer.Ordinal,
	n = Text.Length(s),
	sub = List.TransformMany(
	{0..n-1},
	each {_+1..n},

m-dekorte / errorReport.pq

Created January 26, 2025 11:24

errorReport M function | Creates a record with overall error count and detailed error reporting table.

	let
	fxErrorReport = (myTable as table, optional keyCols as list, optional scanCols as list ) as record =>
	let
	colNames = Table.ColumnNames( myTable ),
	validKeys = List.Intersect( { colNames, keyCols ?? {} } ),
	hasKeys = List.Count( validKeys ) >0,
	selCols = List.Intersect( { colNames, scanCols ?? {} } ),
	nTable = Table.SelectColumns( myTable, if List.Count( selCols ) >0 then validKeys & selCols else colNames ),
	reservedNames = { "Name", "Errors", "Reason", "Message", "Detail", "rowIndex", "nCol" },
	nameViolations = List.Intersect( { colNames, reservedNames } ),

m-dekorte / profileWithErrorCount.pq

Created January 26, 2025 11:18

profileWithErrorCount M function | Creates a table profile with statistics, including an error count for all columns.

	(t as table) as table =>
	Table.Profile(t,
	{{
	"ErrorCount",
	each true,
	each Table.RowCount(Table.SelectRowsWithErrors(Table.FromColumns({_})))
	}}
	)

m-dekorte / errorReplacer.pq

Created January 26, 2025 11:11

errorReplacer M function | Substitute error values in all or listed scanColumns with a specified value or null if no replacement is provided.

	(t as table, optional replacementValue as any, optional scanCols as list) as table =>
	let
	colNames = Table.ColumnNames(t),
	selCols = List.Intersect({colNames, scanCols ?? {}}),
	replErrors = Table.ReplaceErrorValues(t,
	List.Transform(
	if List.IsEmpty(selCols) then colNames else selCols,
	each {_, replacementValue}
	)
	)

m-dekorte / excel-lambda-trimChar.txt

Created November 11, 2024 10:56

λ Trim a leading and trailing, leading or trailing character repeatedly.

	trimChar = LAMBDA(str, [char], [mode],
	LET(
	charToTrim, IF(ISOMITTED(char), " ", LEFT(char, 1)),
	trimMode, IF(OR(mode = 1, mode = 2, mode = 3), mode, 1),
	// Trim functions
	trimStart, LAMBDA(s,
	REDUCE(
	s,
	SEQUENCE(LEN(s)),
	LAMBDA(a, _,

m-dekorte / fillRightOrLeft_Example.pq

Last active November 12, 2024 08:34

fillRightOrLeft Example | fill values right (or left) with optional fill columns and number of top rows.

	let
	fillRightOrLeft = ( tbl as table, optional fillCols as list, optional topRows as number, optional fillLeft as logical) as table =>
	let
	fillFunction = if fillLeft ?? false then Table.FillUp else Table.FillDown,
	t = if topRows = null then tbl else Table.FirstN(tbl, topRows),
	allCols = Table.ColumnNames( tbl ),
	fillSideways = Table.TransformRows( t,
	each _ & Record.FromTable(
	fillFunction( Record.ToTable(
	Record.SelectFields(_, fillCols ?? allCols, MissingField.Ignore)

m-dekorte / fillRightOrLeft.pq

Created November 4, 2024 23:02

fillRightOrLeft M function | fill values right (or left) with optional fill columns and number of top rows.

	( tbl as table, optional fillCols as list, optional topRows as number, optional fillLeft as logical) as table =>
	let
	fillFunction = if fillLeft ?? false then Table.FillUp else Table.FillDown,
	t = if topRows = null then tbl else Table.FirstN(tbl, topRows),
	allCols = Table.ColumnNames( tbl ),
	fillSideways = Table.TransformRows( t,
	each _ & Record.FromTable(
	fillFunction( Record.ToTable(
	Record.SelectFields(_, fillCols ?? allCols, MissingField.Ignore)
	), {"Value"})

Melissa de Korte m-dekorte