5 arguments were passed to a function which expects between 2 and 4

[johnlhaase]

Hello

I am trying to get my report refreshed and I keep getting this error while using Gateway. I went back into my query to see if there is a questionable reference to a table and I cannot find anything. Part of the issue is I am not sure what I need to look for.

Thanks

John

[Greg_Deckler]

@johnlhaase Can you post the details of the error. Also, the M code you are using. Thanks.

[johnlhaase]

 Greg below is the MCode from the main table

Source = Excel.Workbook(File.Contents("C:\Users\jhaase\OneDrive - kalittaair.com\BI Reports\2020 Dates.xlsx"), null, true),
    #"2020 Dates_Sheet" = Source{[Item="2020 Dates",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"2020 Dates_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Start of Month", type date}, {"Date", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Today's date", each DateTime.Date(DateTime.LocalNow())),
    #"Extracted Date" = Table.TransformColumns(#"Added Custom1",{}),
    #"Added FutureDates" = Table.AddColumn(#"Extracted Date", "FutureDates", each if [Date]>[#"Today's date"] then "Future" else "Past"),
    #"Filtered Future" = Table.SelectRows(#"Added FutureDates", each ([FutureDates] = "Past")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Future",{"FutureDates"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns1", {"Start of Month"}, #"12-Month CrewCount", {"Months"}, "Crew Count Report", JoinKind.LeftOuter),
    #"Expanded Crew Count Report" = Table.ExpandTableColumn(#"Merged Queries", "Crew Count Report", {"EmpID", "DisplayName", "Fleet"}, {"EmpID", "DisplayName", "Fleet"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Crew Count Report", {"Date", "EmpID"}, #"Rotation Flag Report", {"Date", "EMPNumber"}, "Rotation Flag Report", JoinKind.LeftOuter),
    #"Expanded Rotation Flag Report" = Table.ExpandTableColumn(#"Merged Queries1", "Rotation Flag Report", {"PayType", "Rotation"}, {"PayType", "Rotation"}),
    #"Filtered On ShiftOn Rotations" = Table.SelectRows(#"Expanded Rotation Flag Report", each ([Rotation] = "On" or [Rotation] = "Shift On")),
    #"Merged Queries2" = Table.NestedJoin(#"Filtered On ShiftOn Rotations", {"Date", "EmpID"}, #"Crew Flights Information", {"Date", "EMP_ID"}, "Crew Flights Information", JoinKind.LeftOuter),
    #"Expanded Crew Flights Information" = Table.ExpandTableColumn(#"Merged Queries2", "Crew Flights Information", {"Flight"}, {"Flight"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded Crew Flights Information", {"Date", "EmpID"}, #"Crew Scheduling Detail-Training", {"Date", "EmpID"}, "Crew Scheduling Detail-Training", JoinKind.LeftOuter),
    #"Expanded Crew Scheduling Detail-Training" = Table.ExpandTableColumn(#"Merged Queries3", "Crew Scheduling Detail-Training", {"Code"}, {"Code"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Crew Scheduling Detail-Training",{{"Code", "DutyCode"}}),
    #"Merged Queries4" = Table.NestedJoin(#"Renamed Columns", {"Date", "EmpID"}, #"Crew Activity Report-Astro", {"Date", "EMPID"}, "Jul20 Crew Activity Report-Astro", JoinKind.LeftOuter),
    #"Expanded Jul20 Crew Activity Report-Astro" = Table.ExpandTableColumn(#"Merged Queries4", "Jul20 Crew Activity Report-Astro", {"Code"}, {"Code"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Jul20 Crew Activity Report-Astro",{{"Code", "Activity Code"}}),
    #"Added Assignment Code" = Table.AddColumn(#"Renamed Columns1", "Assignment", each if [Flight] <> null then 27 else if [DutyCode] <> null then [DutyCode] else if [Activity Code] <> null then [Activity Code] else "No Assignment"),
    #"Grouped Rows" = Table.Group(#"Added Assignment Code", {"Start of Month", "Date", "Today's date", "EmpID", "DisplayName", "Fleet", "PayType", "Rotation"}, {{"Assignment", each List.Max([Assignment]), type number}, {"Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries5" = Table.NestedJoin(#"Grouped Rows", {"Assignment"}, AssignmentCodes, {"Index"}, "AssignmentCodes", JoinKind.LeftOuter),
    #"Expanded AssignmentCodes1" = Table.ExpandTableColumn(#"Merged Queries5", "AssignmentCodes", {"Assignment - Copy"}, {"Assignment - Copy"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded AssignmentCodes1",{"Assignment"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Duplicates",{{"Assignment - Copy", "Assignment"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns3", each ([PayType] <> "CQ Pay" and [PayType] <> "Sim Instructor Pay" and [PayType] <> "Transition/Upgrade Training Pay" and [PayType] <> "Vacation Pay")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1",null,"Rest or Unassigned",Replacer.ReplaceValue,{"Assignment"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Date", Order.Descending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Start of Month", "Today's date", "Date", "EmpID", "DisplayName", "Fleet", "PayType", "Rotation", "Count", "Assignment"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Today's date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Days From Today", each [#"Today's date"]-[Date]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Days From Today", Int64.Type}})

[johnlhaase]

Below is the Data Source Error.

I also have this DAX Column

DateRange =

IF ( DAY(Population[Date] ) = DAY(TODAY()),

"Current Date", DAY(TODAY()-DAY(Population[Date])) & " Day(s) Ago")

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"5 arguments were passed to a function which expects between 2 and 4."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Arguments","detail":{"type":1,"value":"{Table.FromRecords({}), \"Index\", 1, 1, number}"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}}],"exceptionCulprit":1}}} Table: Crew Flights Information.

[Greg_Deckler]

@johnlhaase - 

DateRange = 
  IF( 
    DAY(Population[Date] ) = DAY(TODAY()),
    "Current Date", 
    DAY(TODAY())-DAY(Population[Date]) & " Day(s) Ago"
  )

[johnlhaase · Accepted solution]

Hello

After a significant amount of time trying to find the error. I went back and re-read the meassage which refered to the table it was having an issue with. Although I check this table several time it ended up being the culprit. The table is imported as a CSV with several blank rows before the header. I had slipped in a data change to a column prior to "Using First Row as Header" which casued the issue. Always remember to read the error message closely since its the clue to the problem!!!!

Thanks for your help.

John Haase

aka Johnny X

[johnlhaase]

I do not see you solution. Did you send one?

John