lcorbasson’s gists

lcorbasson / powershell-refresh-excel-powerqueries.ps1

Created June 19, 2025 09:12 — forked from Marc-Anderson/powershell-refresh-excel-powerqueries.ps1

Refresh excel power queries with powershell and save

	# warning:
	# this is a rough script for refreshing excel files with power query connections
	# it should be adjusted to fit your needs and environment

	# usage:
	# 1. save file as refresh_excel_file.ps1
	# 2. open the run dialog with WIN+R
	# 3.1. use the below command to run the script with the path to the excel file(change the filepath to your excel file)
	# powershell -NoExit -File "C:\Users\username\Desktop\refresh_excel_file.ps1" -Path "C:\Users\username\Desktop\file_with_queries.xlsx"
	# 3.2. you can also run the script from a powershell window directly

lcorbasson / Text_Normalize (Example).M

Created April 3, 2025 16:17

	let
	TextNormalize = (someText as any) => if Value.Is(someText, type text) then Text.Trim(Text.Upper(Text.Replace(Text.Replace(someText, "#(lf)", " "), " ", " ")), {" ", "[", "]"}) else someText
	in
	TextNormalize

lcorbasson / Table_RelativeReorderColumns.M

Created April 3, 2025 16:17

	let
	Function =
	(data as table, columnsToOrderLeft as list, optional columnsToOrderRight as list) as table =>
	let
	CurrentOrder = Table.ColumnNames(data),
	ReorderLeft = columnsToOrderLeft,
	ReorderRight = columnsToOrderRight ?? {},
	OrderedColumnsRemoved = List.RemoveItems(CurrentOrder, ReorderLeft & ReorderRight),
	NewOrdering = ReorderLeft & OrderedColumnsRemoved & ReorderRight,
	Reordered = Table.ReorderColumns(data, NewOrdering)

lcorbasson / Table_LookForValues.M

Created April 3, 2025 16:16

	let
	Table_LookForValues = (sourceTable as table, valuesToLookFor as list, normalizationFunction as function, columnsToLookIn as list) =>
	let foo = List.Accumulate(valuesToLookFor, sourceTable, (previous_table, current_value) =>
	if List.Contains(Table.ColumnNames(previous_table), current_value)
	then previous_table
	else Table.AddColumn(previous_table, current_value, each List.Contains(Record.FieldValues(Record.TransformFields(Record.SelectFields(_, columnsToLookIn), List.Transform(columnsToLookIn, each {_, normalizationFunction}))), current_value))
	)
	in foo
	in
	Table_LookForValues

lcorbasson / textEquals.M

Last active March 17, 2025 15:57

	let
	textEquals = (values as list) as logical =>
	let
	textEquals = List.Count(List.Distinct(
	List.ReplaceMatchingItems(
	values,
	{{null, ""}}
	)
	)) <= 1
	in

lcorbasson / addFlagColumn_TextEquals.M

Created February 6, 2025 23:39

	let
	addFlagColumn_TextEquals = (source_table as table, name_of_the_flag_column as text, names_of_the_columns_to_compare as list) as table =>
	let
	indexedTable = Table.AddIndexColumn(source_table, "Index", 0, 1),
	flaggedTable = Table.AddColumn(indexedTable, name_of_the_flag_column, each
	if List.Distinct(
	List.ReplaceMatchingItems(
	Record.ToList(Table.SelectColumns(indexedTable, names_of_the_columns_to_compare){[Index]}),
	{{null, ""}}
	)

lcorbasson / removeUncleanColumns.M

Last active March 17, 2025 15:57

	let
	removeUncleanColumns = (source_table as table) as table =>
	let
	columnsToKeep = Table.ColumnsOfType(source_table, {
	type nullable logical, // qui classifie les valeurs true et false.
	type nullable number, // qui classifie les valeurs numériques.
	type nullable time, // qui classifie les valeurs d’heure.
	type nullable date, // qui classifie les valeurs de date.
	type nullable datetime, // qui classifie les valeurs datetime.
	type nullable datetimezone, // qui classifie les valeurs datetimezone.

lcorbasson / fullyExpandTableColumn.M

Last active March 17, 2025 15:57

	let
	fullyExpandTableColumn = (source_table as table, column_name as text, optional prefix as nullable text, optional overwrite_existing_columns as logical) as table =>
	let
	calculatedPrefix = if prefix <> null then prefix else column_name,
	calculatedOverwrite = if overwrite_existing_columns <> null then overwrite_existing_columns else false,
	prefixAndDot = if calculatedPrefix <> "" then calculatedPrefix & "." else calculatedPrefix,
	combinedTable = Table.Combine(List.Select(Table.Column(source_table, column_name), each _ <> null)),
	columnNames = Table.ColumnNames(combinedTable),
	sortedColumnNames = List.Sort(columnNames),
	prefixedSortedColumnNames = List.Transform(sortedColumnNames, each prefixAndDot & _),

lcorbasson / transformDotDollarDateColumns.M

Created January 31, 2025 22:01

	let
	transformDotDollarDateColumns = (source_table as table) as table =>
	let
	dotDollarDateColumnNames = List.Select(Table.ColumnNames(source_table), each Text.EndsWith(_, ".$date")),
	dotDollarDateColumnTypes = List.Transform(dotDollarDateColumnNames, each {_, type nullable datetimezone}),
	renamedDotDollarDateColumnNames = List.Transform(dotDollarDateColumnNames, each Text.BeforeDelimiter(_, ".$date", {0, RelativePosition.FromEnd})),
	dotDollarDateColumnRenames = List.Zip({dotDollarDateColumnNames, renamedDotDollarDateColumnNames}),
	transformedTable = Table.TransformColumnTypes(source_table, dotDollarDateColumnTypes),
	// renameTargetsRemovedTable = Table.RemoveColumns(transformedTable, renamedDotDollarDateColumnNames, MissingField.Ignore),
	renamedTable = Table.RenameColumns(transformedTable, dotDollarDateColumnRenames)

lcorbasson / isDotDollarDateRecordColumn.M

Created January 31, 2025 22:01

	let
	isDotDollarDateRecordColumn = (source_table as table, column_name as text) as logical =>
	let
	// dotDollarDateRecordColumnNames = List.Select(Table.ColumnNames(source_table), each Table.RowCount(Table.SelectRowsWithErrors(Table.ExpandRecordColumn(Table.Distinct(Table.SelectColumns(source_table, _)), _, {"$date"}, {_}))) = 0),
	isDotDollarDateRecordColumn = Table.RowCount(Table.SelectRowsWithErrors(Table.ExpandRecordColumn(Table.Distinct(Table.SelectColumns(source_table, column_name)), column_name, {"$date"}, {column_name}))) = 0
	in
	isDotDollarDateRecordColumn
	in
	isDotDollarDateRecordColumn

Loïc Corbasson lcorbasson