Error: We Cannot Convert a value of Type Function to Type list

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
158
I have been trying to create a function to calculate the number of working days (including holidays). I found instructions on how to create fnNetworkdays but I am getting the following error when I try to use it in a query:

Expression.Error: We cannot convert a value of type Function to type List.
Details:
Value=Function
Type=Type

I have no clue what it means. Here is my M coding for the function:

(StartDate as date, EndDate as date) as number =>
let
Source = List.Dates,
#"Invoked Function Source" =
if StartDate <= EndDate then
Source(StartDate,Duration.Days(EndDate-StartDate)+1, Duration.From(1))
else
Source(EndDate, Duration.Days(StartDate-EndDate)+1, Duration.From(1)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Inserted Day of Week" = Table.AddColumn(#"Converted to Table", "Day of Week", each Date.DayOfWeek([Column1],Day.Monday), Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] <> 5 and [Day of Week] <> 6)),
Custom1 = if StartDate <= EndDate then Table.RowCount(#"Filtered Rows") else Table.RowCount(#"Filtered Rows")*(-1)
in
Custom1

Can anyone see where I am going wrong? This is the instructions I followed: https://www.powerquery.training/networkdays/
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
I've had this problem before and it was because you were using a table instead of a list.
Click on the query column heading and select drill down.
 
Upvote 0
Shouldn't the line

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Actually be:
#"Converted to Table" = Table.FromList(#"Invoked Function Source", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 
Upvote 0
maybe try this
Code:
// fnGetParameter
(ParameterName as text) =>
    let
        ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
        ParamRow = Table.SelectRows(ParamSource, each ([Parameter]=ParameterName)),
            Value=
            if Table.IsEmpty(ParamRow)=true
                        then null
                        else Record.Field(ParamRow{0},"Value")
    in
        Value

Code:
// Calendar
let
    startdate = Number.From(fnGetParameter("StartDate")),
    enddate = Number.From(fnGetParameter("EndDate")),

    Source = {startdate..enddate},
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Type = Table.TransformColumnTypes(ToTable,{{"Column1", type date}}),
    Rename = Table.RenameColumns(Type,{{"Column1", "Date"}}),
    Year = Table.AddColumn(Rename, "Year", each Date.Year([Date]), Int64.Type),
    Month = Table.AddColumn(Year, "Month", each Date.Month([Date]), Int64.Type),
    Day = Table.AddColumn(Month, "Day", each Date.Day([Date]), Int64.Type),
    Quarter = Table.AddColumn(Day, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    WeekOfYear = Table.AddColumn(Quarter, "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    WeekOfMonth = Table.AddColumn(WeekOfYear, "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    MonthName = Table.AddColumn(WeekOfMonth, "Month Name", each Date.MonthName([Date]), type text),
    DayName = Table.AddColumn(MonthName, "Day Name", each Date.DayOfWeekName([Date]), type text)
in
    DayName

ParameterValueDateYearMonthDayQuarterWeek of YearWeek of MonthMonth NameDay Name
StartDate10/02/202010/02/20202020210173FebruaryMonday
EndDate29/02/202011/02/20202020211173FebruaryTuesday
12/02/20202020212173FebruaryWednesday
13/02/20202020213173FebruaryThursday
14/02/20202020214173FebruaryFriday
15/02/20202020215173FebruarySaturday
16/02/20202020216173FebruarySunday
17/02/20202020217184FebruaryMonday
18/02/20202020218184FebruaryTuesday
19/02/20202020219184FebruaryWednesday
20/02/20202020220184FebruaryThursday
21/02/20202020221184FebruaryFriday
22/02/20202020222184FebruarySaturday
23/02/20202020223184FebruarySunday
24/02/20202020224195FebruaryMonday
25/02/20202020225195FebruaryTuesday
26/02/20202020226195FebruaryWednesday
27/02/20202020227195FebruaryThursday
28/02/20202020228195FebruaryFriday
29/02/20202020229195FebruarySaturday
 
Upvote 0
I found this forum by following the directions on NetWorkDays - Power Query Training like the OP did. I also ran into the EXACT same error message. Fortunately, someone in the comment section of the original training provided their code which worked for me! Here's the code:

(StartDate as date, EndDate as date) as number =>
let
Source =
(if StartDate <= EndDate then
List.Dates(StartDate,
Duration.Days(EndDate-StartDate),
Duration.From(1))
else
List.Dates(EndDate,
Duration.Days(StartDate-EndDate),
Duration.From(1))
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Inserted Day of Week" = Table.AddColumn(#"Converted to Table", "Day of Week", each Date.DayOfWeek([Column1], Day.Monday), Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] <> 5 and [Day of Week] <> 6)),
Custom1 = if StartDate <= EndDate then Table.RowCount(#"Filtered Rows") else Table.RowCount(#"Filtered Rows") * (-1)
in
Custom1
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top