Expression.Error: There is an unknown identifier. Did you use the [field]??? Function Error

[CarlBuckley]

HI All, I have an issue with some syntax causing an expression error

I have a table that i have grouped by project number called reporting periods [image1] and then i have invoked a function that creates a calendar based off the data within the reporting periods table. the function seems to work however i have an issue that is not allowing me to create the fiscal year column [image 2]. I have attached the syntax of the function below.. all is working until the areas i have highlighted in read?  

any help would be very much appreciated.

let DateInput = ( StartDate as date, EndDate as date) as table =>
let
//Create lists of month and day names for use later on
MonthList = {"January", "February", "March", "April", "May", "June"
, "July", "August", "September", "October", "November", "December"},
DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},
//Find the number of days between the end date and the start date
NumberOfDates = Duration.Days(EndDate-StartDate)+1,
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
//Turn this list into a table
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
//Caste the single column in the table to type date
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
//Add custom columns for day of month, month number, year
DayOfMonth = Table.TransformColumnTypes(Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])), {{"DayOfMonth", Int64.Type}}),
MonthNumber = Table.TransformColumnTypes(Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])), {{"MonthNumberOfYear", Int64.Type}}),
Year = Table.TransformColumnTypes(Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])), {{"Year", Int64.Type}}),
DayOfWeekNumber = Table.TransformColumnTypes(Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1), {{"DayOfWeekNumber", Int64.Type}}),
//Since Power Query doesn't have functions to return day or month names,
//use the lists created earlier for this
MonthName = Table.TransformColumnTypes(Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}), {{"MonthName", type text}}),
DayName = Table.TransformColumnTypes(Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}), {{"DayName", type text}}),
//Add a column that returns true if the date on rows is the current date
IsToday = Table.TransformColumnTypes(Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date])), {{"IsToday", type logical}}),
IsFuture = Table.TransformColumnTypes(Table.AddColumn(IsToday, "IsFuture", each if [Date] <= Date.From(DateTime.LocalNow()) then false else true), {{"IsFuture", type logical}}),

FiscalYearCalc = (Date) =>
Table.First(
Table.Sort(
Table.SelectRows(
[ReportingPeriods],
each Date >= [Start] and [Fiscal Period] = 1
),
{{"Start", Order.Descending}})
)[ReportingPeriods][Fiscal Year],

FiscalYear = Table.AddColumn(IsFuture, "FiscalYear", each FiscalYearCalc([Date]))

in
FiscalYear
//PrettyFiscalWeek
in
DateInput

[v-cgao-msft · Accepted solution]

Hi @CarlBuckley ,

In your code, you're trying to access 'ReportingPeriods' inside the 'FiscalYearCalc' function, but it's not clear where this table is coming from. If 'ReportingPeriods' is a table that exists outside of your 'let' expression, you might need to pass it as a parameter to your function.

let
  DateInput = (StartDate as date, EndDate as date, ReportingPeriods as table) as table =>
    let
      //Create lists of month and day names for use later on
      MonthList = {
        "January", 
        "February", 
        "March", 
        "April", 
        "May", 
        "June", 
        "July", 
        "August", 
        "September", 
        "October", 
        "November", 
        "December"
      }, 
      DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"}, 
      //Find the number of days between the end date and the start date
      NumberOfDates = Duration.Days(EndDate - StartDate) + 1, 
      //Generate a continuous list of dates from the start date to the end date
      DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)), 
      //Turn this list into a table
      TableFromList = Table.FromList(
        DateList, 
        Splitter.SplitByNothing(), 
        {"Date"}, 
        null, 
        ExtraValues.Error
      ), 
      //Caste the single column in the table to type date
      ChangedType = Table.TransformColumnTypes(TableFromList, {{"Date", type date}}), 
      //Add custom columns for day of month, month number, year
      DayOfMonth = Table.TransformColumnTypes(
        Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])), 
        {{"DayOfMonth", Int64.Type}}
      ), 
      MonthNumber = Table.TransformColumnTypes(
        Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])), 
        {{"MonthNumberOfYear", Int64.Type}}
      ), 
      Year = Table.TransformColumnTypes(
        Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])), 
        {{"Year", Int64.Type}}
      ), 
      DayOfWeekNumber = Table.TransformColumnTypes(
        Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date]) + 1), 
        {{"DayOfWeekNumber", Int64.Type}}
      ), 
      //Since Power Query doesn't have functions to return day or month names,
      //use the lists created earlier for this
      MonthName = Table.TransformColumnTypes(
        Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear] - 1}), 
        {{"MonthName", type text}}
      ), 
      DayName = Table.TransformColumnTypes(
        Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber] - 1}), 
        {{"DayName", type text}}
      ), 
      //Add a column that returns true if the date on rows is the current date
      IsToday = Table.TransformColumnTypes(
        Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date])), 
        {{"IsToday", type logical}}
      ), 
      IsFuture = Table.TransformColumnTypes(
        Table.AddColumn(
          IsToday, 
          "IsFuture", 
          each if [Date] <= Date.From(DateTime.LocalNow()) then false else true
        ), 
        {{"IsFuture", type logical}}
      ), 
      FiscalYearCalc = (Date, ReportingPeriods) =>
        Table.First(
          Table.Sort(
            Table.SelectRows(ReportingPeriods, each Date >= [Start] and [Fiscal Period] = 1), 
            {{"Start", Order.Descending}}
          )
        )[ReportingPeriods][Fiscal Year], 
      FiscalYear = Table.AddColumn(
        IsFuture, 
        "FiscalYear", 
        each FiscalYearCalc([Date], ReportingPeriods)
      )
    in
      FiscalYear
//PrettyFiscalWeek
inDateInput

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[CarlBuckley]

HI, Thankyou for the help... I had to remove the [ReportingPeriods] from [ReportingPeriods][Fiscal Year] as the TableFirst option but other than that it was perfect... thankyou

 FiscalYearCalc = (Date, ReportingPeriods) =>
        Table.First(
          Table.Sort(
            Table.SelectRows(ReportingPeriods, each Date >= [Start] and [Fiscal Period] = 1), 
            {{"Start", Order.Descending}}
          )
        )[ReportingPeriods][Fiscal Year], 

[lbendlin]

I would recommend you go to powerqueryformatter.com to get your code into a format that is easier to follow. The error is likely caused by nested references where you forgot to specify the outer reference in the inner loop.

Personal opinion:  Do not attempt to create custom calendar tables in Power Query or DAX. Instead, use an external table that has all the required computations pre-done.