Skip to content

Instantly share code, notes, and snippets.

@ninmonkey
Created July 19, 2025 21:22
Show Gist options
  • Select an option

  • Save ninmonkey/1552343344cb5d99b995551b5fdbecb4 to your computer and use it in GitHub Desktop.

Select an option

Save ninmonkey/1552343344cb5d99b995551b5fdbecb4 to your computer and use it in GitHub Desktop.
Merge multiple `Table.AddRows` and schema transforms into fewer steps
let
startDate = DateTime.Date( DateTime.LocalNow() ),
endingOffset = 1000, // in days
endingOffsetDate = Date.AddDays( startDate, endingOffset ),
allDays = List.Transform(
{ Number.From(startDate)..Number.From(endingOffsetDate) },
each { Date.From( _ ) }
),
Source = #table(
type table [ Date = date ], allDays ),
addDimensions = Table.AddColumn(
Source, "Dimensions",
(row) => [
Year = Date.Year( row[Date] ),
Month = Date.Month( row[Date] ),
Quarter = Date.QuarterOfYear( row[Date] ),
// reference: https://learn.microsoft.com/en-us/powerquery-m/custom-date-and-time-format-strings
MonthPadded = Date.ToText( row[Date], [
Culture = "en-US", Format = "MM" ] ),
// if you want "202407"
YearMonth = Date.ToText( row[Date], [
Culture = "en-US", Format = "yyyyMM" ] )
],
rowSchema
),
rowSchema = type [
Year = Int64.Type,
Month = Int64.Type,
Quarter = Int64.Type,
MonthPadded = text,
YearMonth = text
],
all_column_names = {"Year", "Month", "Quarter", "MonthPadded", "YearMonth"}, // or dynamically get them using Record.FieldNames()
#"Expanded Dimensions" = Table.ExpandRecordColumn( addDimensions, "Dimensions", all_column_names, all_column_names)
in
#"Expanded Dimensions"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment