I have a simple calendar table dataflow (create a list of dates, add columns based on date). I'll admit I copied some code online and tweaked it for my requirements.
Code:
let
FromYear = Date.Year(Date.AddYears(Date.From(DateTime.LocalNow()),-2)),
ToYear = Date.Year(Date.From(DateTime.LocalNow())),
StartofFiscalYear = 8,
FiscalMonthBaseIndex = 13-StartofFiscalYear,
adjustedFiscalMonthBaseIndex = if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
firstDayofWeek = Day.Monday,
FromDate = #date(FromYear,1,1),
ToDate = #date(ToYear,12,31),
Source = List.Dates(
#date(FromYear,1,1),
Duration.Days(#date(ToYear,12,31)-#date(FromYear,1,1))+1,
#duration(1,0,0,0)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
#"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
#"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day Name", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Day of Year", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
#"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Year", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
#"Added FiscalBaseDate" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added FiscalBaseDate", {{"FiscalBaseDate", type date}}),
#"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1", {{"Year.1", "Fiscal Year"}}),
#"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1", {{"Quarter.1", "Fiscal Quarter"}}),
#"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1", {{"Month.1", "Fiscal Month"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3", {"FiscalBaseDate"})
in
Source
and it works, I get no errors while building it. When I refresh it, it shows this error: Error: Expression.Error: We cannot convert a value of type List to type Table.
and when I look at the advanced editor I see these additional lines just before the (in...source) at the end:
#"Convert to table" = Table.FromValue(#"Removed Columns"),
#"Transform columns" = Table.TransformColumnTypes(#"Convert to table", {{"Value", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Value", null}})
I'll admit i know next to nothing about M language and am still feeling around in the dark, but I don't think it should be trying to convert it to a table again. If anyone can give me pointers or show me what I'm doing wrong, I would greatly appreciate it.
Edited: Readability
