Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
CarlBuckley
Frequent Visitor

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

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

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

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

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.

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors