"Expression.Error: There weren't enough elements in the enumeration to complete the operation." when expanding nested table

[Jinxster]

Hello,

I'm having trouble identifying the issue with a query. I set-up several queries that pull data from a SQL Server database, process them and outputs a table in excel. These queries work as intended most of the time. I tried to troubleshoot when they don't. Walking back, I locked down where they get stuck but I cannot see what's the issue.
At some point I merge 2 queries (called TASK and TASKACTV). Up to this point, both queries work just fine. When I try to expand the table resulted from the merge I get the error message below.

I'm lost. To my understanding, even if the merge had no matches, it should just expand to columns with nulls. But it's not the case.
Some additional information: the database is for Primavera P6 and I pull data for a project id you can select. This issue I encountered with only 1 project so far. Tested with 5-6 other projects and it works just fine. I have other files with different queries that pull data from this project and they work just fine, so it is not the project per say. Cannot narrow down the problem with the project.
Working at query TASK, I encountered this issue also when trying to pivot some columns. Again, I could see no issue. In the end, I ended by doing the pivoting in another query (TASKACTV) and it worked. Why it would work in the TASKACTV query and not in the TASK query I don't know. As you can see merging these 2 queries leads to the same error sometimes.

I included the code for both queries.

//TASK

let
    Source = PROJECT,
    #"Removed Columns" = Table.RemoveColumns(Source,{"proj_id"}),
    #"Expanded TASK" = Table.ExpandTableColumn(#"Removed Columns", "TASK", {"wbs_id", "task_type", "task_code", "task_name", "target_work_qty", "act_start_date", "act_end_date", "early_start_date", "early_end_date", "TASKRSRC"}, {"wbs_id", "task_type", "task_code", "task_name", "target_work_qty", "act_start_date", "act_end_date", "early_start_date", "early_end_date", "TASKRSRC"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded TASK", each ([task_type] <> "TT_Mile" and [task_type] <> "TT_Finmile")),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Start Date", each if [act_start_date] = null then Date.From([early_start_date]) else Date.From([act_start_date]), Date.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Finish Date", each if [act_end_date]=null then Date.From([early_end_date]) else Date.From([act_end_date]), Date.Type),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"task_type", "act_start_date", "act_end_date", "early_start_date", "early_end_date"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Removed Columns2",ReorderList(#"Removed Columns2",{"Start Date", "Finish Date"},"task_name")),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns2", {"wbs_id"}, PROJWBS, {"wbs_id"}, "PROJWBS", JoinKind.LeftOuter),
    #"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{"wbs_id"}),
    #"Expanded PROJWBS" = Table.ExpandTableColumn(#"Removed Columns1", "PROJWBS", {"WBS Name", "WBS Code"}, {"WBS Name", "WBS Code"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded PROJWBS",ReorderList(#"Expanded PROJWBS",{"WBS Code","WBS Name"},"Project ID")),
    #"Merged Queries1" = Table.NestedJoin(#"Reordered Columns", {"task_code"}, TASKACTV, {"task_code"}, "TASKACTV", JoinKind.LeftOuter), //Works fine up to this point
    #"Expanded TASKACTV1" = Table.ExpandTableColumn(#"Merged Queries1", "TASKACTV", {"Project Stage", "SOW ID", "Discipline", "Activity Responsible"}, {"Project Stage", "SOW ID", "Discipline", "Activity Responsible"}), //Error   
    #"Expanded TASKRSRC" = Table.ExpandTableColumn(#"Expanded TASKACTV", "TASKRSRC", {"target_qty", "rate_type", "ROLES"}, {"target_qty", "rate_type", "ROLES"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded TASKRSRC", {"rate_type"}, Table_Cost_Rate, {"rate_type"}, "Table_Cost_Rate", JoinKind.LeftOuter),
    #"Expanded Table_Cost_Rate" = Table.ExpandTableColumn(#"Merged Queries3", "Table_Cost_Rate", {"PMT Role"}, {"PMT Role"}),
    #"Expanded ROLES" = Table.ExpandRecordColumn(#"Expanded Table_Cost_Rate", "ROLES", {"role_short_name"}, {"role_short_name"}),
    #"Added Custom" = Table.AddColumn(#"Expanded ROLES", "Department", each Text.BetweenDelimiters([role_short_name],"(",")"), Text.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Role", each if [PMT Role] = null then Text.BeforeDelimiter([role_short_name]," ") else [PMT Role], Text.Type),
    #"Removed Columns5" = Table.RemoveColumns(#"Added Custom1",{"rate_type", "role_short_name", "PMT Role"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns5",ReorderList(#"Removed Columns5",{"Department","Role","target_qty"},"target_work_qty")),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"task_code", "Activity ID"}, {"task_name", "Activity Name"}, {"target_work_qty", "Activity Budget"}, {"target_qty", "Resource Budget"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Activity ID", Order.Ascending}})
in
    #"Sorted Rows"

//TASKACTV

let
    Source = PROJECT,
    #"Expanded TASK" = Table.ExpandTableColumn(Source, "TASK", {"task_code", "task_type", "TASKACTV"}, {"task_code", "task_type", "TASKACTV"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded TASK", each ([task_type] <> "TT_Mile" and [task_type] <> "TT_Finmile")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"proj_id", "Project ID", "task_type"}),
    #"Expanded TASKACTV" = Table.ExpandTableColumn(#"Removed Columns", "TASKACTV", {"ACTVCODE", "ACTVTYPE"}, {"ACTVCODE", "ACTVTYPE"}),
    #"Expanded ACTVCODE" = Table.ExpandRecordColumn(#"Expanded TASKACTV", "ACTVCODE", {"actv_code_id"}, {"actv_code_id"}),
    #"Expanded ACTVTYPE" = Table.ExpandRecordColumn(#"Expanded ACTVCODE", "ACTVTYPE", {"actv_code_type"}, {"actv_code_type"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded ACTVTYPE", each ([actv_code_type] = "Activity Responsible" or [actv_code_type] = "Discipline" or [actv_code_type] = "Project Stage" or [actv_code_type] = "SOW ID")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"actv_code_id"}, ACTVTYPE, {"actv_code_id"}, "ACTVTYPE", JoinKind.LeftOuter),
    #"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{"actv_code_id"}),
    #"Expanded ACTVTYPE1" = Table.ExpandTableColumn(#"Removed Columns1", "ACTVTYPE", {"Value"}, {"Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded ACTVTYPE1", List.Distinct(#"Expanded ACTVTYPE1"[actv_code_type]), "actv_code_type", "Value"),
    #"Merged Queries1" = Table.NestedJoin(#"Pivoted Column", {"Activity Responsible"}, Table.SelectRows(ACTVTYPE, each [Name]="Activity Responsible"), {"Value"}, "ACTVTYPE", JoinKind.LeftOuter),
    #"Expanded ACTVTYPE2" = Table.ExpandTableColumn(#"Merged Queries1", "ACTVTYPE", {"Description"}, {"Description"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded ACTVTYPE2",{"Activity Responsible"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Description", "Activity Responsible"}})
in
    #"Renamed Columns"

 

[RoryA]

Given that you are pivoting columns, your resulting column names will be dependent on what data is there - perhaps that's the problem?

 

[Jinxster]

Given that you are pivoting columns, your resulting column names will be dependent on what data is there - perhaps that's the problem?

As mentioned, the query where I'm currently pivoting (TASKACTV) works just fine. I've inspected the data and is ok, as it should be, given that the source is ok.