Fiscal Year

[KevinMorneault]

Hi All

I have a query done in power query for the fiscal year, the query is working fine, i decided to add more criteria and did validation via copilot, which should be work. But  for some reason i keep getting the error message 

Expression.Error: We cannot convert the value #date(2023, 4, 😎 to type Table.
Details:
Value=08/04/2023
Type=[Type]

Which in the query  i have the same line 

  // Filter rows based on the date comparison
    FilteredRows = Table.SelectRows(DebugSource, each [Current Renewal EndDate] > #date(2023, 4, 1)),

I also make sure  the appropriate column are as well under the correct format  as Date  which they are. 

Any one can guide me on this.

Tks

[lbendlin]

What does this have to do with Fiscal years?

Please show more of the query.  Re-check that the [Current Renewal EndDate] column truly is a Date column.

[KevinMorneault]

Hi  

Tks for your reply 

Just as an awarness i use the same query which initially was just showing Previous fiscal year, Current Fiscal year and Next fiscal year on a label. 

We wanted to improve by adding more for example if they select SELECT ALL.   this is the adjustment we did to it, since then not able to make the query work. 

Here's the query

let
// Your existing data source
Source = [Calendar Table.Date],

// Ensure the date column is of type date
SourceWithDateType = Table.TransformColumnTypes(Source, {{"Current Renewal EndDate", type date}}),

// Debugging Step: Check the transformed source
DebugSource = SourceWithDateType,

// Filter rows based on the date comparison
FilteredRows = Table.SelectRows(DebugSource, each [Current Renewal EndDate] > #date(2023, 4, 1)),

// Debugging Step: Check the filtered rows
DebugFilteredRows = FilteredRows,

// Get the current year
CurrentYear = Date.Year(DateTime.LocalNow()),

// Find the maximum end date
MaxEndDate = List.Max(DebugFilteredRows[Current Renewal EndDate]),

// Get the year of the maximum end date
EndYear = Date.Year(MaxEndDate),

// Define the start year
StartYear = 2023,

// Add FiscalYearLabel column
AddFiscalYearLabel = Table.AddColumn(DebugFilteredRows, "FiscalYearLabel", each
let
SelectedYear = Date.Year([Current Renewal EndDate]),
Label = if SelectedYear = CurrentYear - 1 then "Previous Fiscal Year"
else if SelectedYear = CurrentYear then "Current Fiscal Year"
else if SelectedYear = CurrentYear + 1 then "Next Fiscal Year"
else if SelectedYear = CurrentYear + 2 then "2 Years from Now"
else "Other"
in
Label
),

// Create a string representing all fiscal years
AllFiscalYears = Text.From(StartYear) & "-" & Text.From(EndYear),

// Add AllLabel column
AddAllLabel = Table.AddColumn(AddFiscalYearLabel, "AllLabel", each
if [FiscalYearLabel] = "Other" then "ALL - " & AllFiscalYears else [FiscalYearLabel]
),

// Add BeginFiscalYear and EndFiscalYear columns
AddFiscalYearRange = Table.AddColumn(AddAllLabel, "BeginFiscalYear", each
if [FiscalYearLabel] = "Select All" then StartYear else Date.Year([Current Renewal EndDate])),
AddFiscalYearRangeEnd = Table.AddColumn(AddFiscalYearRange, "EndFiscalYear", each
if [FiscalYearLabel] = "Select All" then EndYear else Date.Year([Current Renewal EndDate]))
in
AddFiscalYearRangeEnd

Yes i did check and confirm the contract renewal enddate is formated as a date

[KevinMorneault]

This is the line which we have the issue 

// Filter rows based on the date comparison
FilteredRows = Table.SelectRows(DebugSource, each [Current Renewal EndDate] > #date(2023, 4, 1)),

[v-xinruzhu-msft]

Hi @KevinMorneault 

Please check that if  you have error in date column,

If it has errors, it will display the information when you filter it.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

cannot reproduce

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31DcyMDJWitWJVjJB5pgic8zgnFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Current Renewal EndDate" = _t]),
    SourceWithDateType = Table.TransformColumnTypes(Source,{{"Current Renewal EndDate", type date}}),
    DebugSource = SourceWithDateType,
    #"Filtered Rows" = Table.SelectRows(DebugSource, each [Current Renewal EndDate] > #date(2023, 4, 1))
in
    #"Filtered Rows"