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

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
155
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/
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

bobby_smith

Board Regular
Joined
Apr 16, 2014
Messages
80
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.
 

peter789

Board Regular
Joined
Nov 20, 2016
Messages
114
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),
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,716
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
 

Cubelife

New Member
Joined
Feb 21, 2020
Messages
4
Office Version
2016
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,089,979
Messages
5,411,636
Members
403,383
Latest member
Excelacity

This Week's Hot Topics

Top