Scribd
Upload
9 views1 page

Nim AddMonths for Date Calculations

This document outlines a Power Query M script that generates a calendar table starting from January 1, 2019, and extending three months from the current date. It includes various transformations to create date-related columns such as DateKey, Year, Quarter, Month name, and Day of the week. The final output is a table that merges fiscal year and quarter information for sorting purposes.

Uploaded by

Rocio Cisterna
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
9 views1 page

Nim AddMonths for Date Calculations

This document outlines a Power Query M script that generates a calendar table starting from January 1, 2019, and extending three months from the current date. It includes various transformations to create date-related columns such as DateKey, Year, Quarter, Month name, and Day of the week. The final output is a table that merges fiscal year and quarter information for sorting purposes.

Uploaded by

Rocio Cisterna
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

let

startDate = #date(2019, 1, 1),


// Edit the number in this step to change the number of months after today for the
last day of the Calendar table.
endDate = [Link]([Link]([Link]()),3),
Dates = [Link](startDate, [Link](endDate - startDate), #duration
(1,0,0,0)),
#"Converted to Table" = [Link](Dates, [Link](), null,
null, [Link]),
#"Renamed Columns" = [Link](#"Converted to Table",{{"Column1",
"Date"}}),
#"Changed Type" = [Link](#"Renamed Columns",{{"Date", type
date}}),
#"Inserted DateKey" = [Link](#"Changed Type", "DateKey", each
[Link]([Date],"yyyyMMdd"), type text),
#"Inserted Year" = [Link](#"Inserted DateKey", "Year", each
[Link]([Date]), [Link]),
#"Inserted Quarter" = [Link](#"Inserted Year", "Quarter", each
[Link]([Date]), [Link]),
#"Inserted FY Quarters" = [Link](#"Inserted Quarter", "FY Quarter", each
if [Quarter] = 1 then "4" else if [Quarter] = 2 then "1" else if [Quarter] = 3 then
"2" else "3", type text),
#"Inserted Month Name" = [Link](#"Inserted FY Quarters", "Month name",
each [Link]([Date]), type text),
#"Inserted Month" = [Link](#"Inserted Month Name", "Month number", each
[Link]([Date]), [Link]),
#"Inserted Day of Month" = [Link](#"Inserted Month", "Day of month",
each [Link]([Date]), [Link]),
#"Inserted Day of Year" = [Link](#"Inserted Day of Month", "Day of
Year", each [Link]([Date]), [Link]),
#"Inserted Day of Week" = [Link](#"Inserted Day of Year", "Day of
Week", each [Link]([Date]), [Link]),
#"Inserted Day Name" = [Link](#"Inserted Day of Week", "Day name",
each [Link]([Date]), type text),
// In Week functions
// 0 represents Sunday start
// 1 represents Monday start
// 2 represents Tuesday start
#"Inserted Week of Year" = [Link](#"Inserted Day Name", "Week of
Year", each [Link]([Date],1), [Link]),
#"Inserted Week of Month" = [Link](#"Inserted Week of Year", "Week of
Month", each [Link]([Date],1), [Link]),
#"Inserted FY start" = [Link](#"Inserted Week of Month", "FY starts",
each [Year] + (if [Month number] > 3 then 0 else -1), type number),
#"Inserted FY" = [Link](#"Inserted FY start", "FY", each [Link]([FY
starts]) & "/" & [Link]([FY starts] + 1), type text),
#"Inserted FY and Quarter" = [Link](#"Inserted FY", "FY and Quarter",
each [Link]({[Link]([FY starts], "en-NZ"), [FY Quarter]}, " Q"), type
text),
#"Inserted Merged Column" = [Link](#"Inserted FY and Quarter", "FY and
Quarter Sort", each [Link]({[Link]([FY starts]), [FY Quarter]}, ""), type
text)
in
#"Inserted Merged Column"

You might also like