Reply
CarlBuckley
Frequent Visitor
Partially syndicated - Outbound

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

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.

 

CarlBuckley_0-1695402785031.png

CarlBuckley_1-1695402809928.png

 

 

 

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

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Syndicated - Outbound

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

View solution in original post

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

Syndicated - Outbound

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

Syndicated - Outbound

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
Super User
Super User

Syndicated - Outbound

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.

 

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)