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

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