Forum Discussion
Anonymous
7 years agoNot applicable
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
I am trying to expand a column and am receiving the error 'Expression.Error: There weren't enough elements in the enumeration to complete the operation. Details: List'. I am not sure what is causing the error as searching the error message it is stating that the issue is when you are pivoting columns and have the same name appearing for a column. The error occurs going from the 'Merged Queries2' step to the 'Expanded Receiving' step. The columns "Receiving-Gms", "Receiving-Att", "Receiving-Yds", "Receiving-Avg", "Receiving-YPG", "Receiving-Lg", "Receiving-TD", "Receiving-FD" do not already exist in the data set
let
Source = Table.Combine({#"ReceivingStats(3)", #"RushingStats(2)", #"PassingStats(1)"}),
#"Removed Duplicates" = Table.Distinct(Source),
#"Merged Queries" = Table.NestedJoin(#"Removed Duplicates",{"SeasonPlayerTeamKey"},Passing,{"SeasonPlayerTeamKey"},"Passing",JoinKind.LeftOuter),
#"Expanded Passing" = Table.ExpandTableColumn(#"Merged Queries", "Passing", {"Pass-Att", "Pass-Cmp", "Pass-Pct", "Pass-Yds", "Pass-YPA", "Pass-TD", "Pass-TD%", "Pass-Int", "Pass-Int%", "Pass-Lg", "Pass-Sack", "Pass-Loss", "Pass-Rate"}, {"Pass-Att", "Pass-Cmp", "Pass-Pct", "Pass-Yds", "Pass-YPA", "Pass-TD", "Pass-TD%", "Pass-Int", "Pass-Int%", "Pass-Lg", "Pass-Sack", "Pass-Loss", "Pass-Rate"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Passing",{"SeasonPlayerTeamKey"},Rushing,{"SeasonPlayerTeamKey"},"Rushing",JoinKind.LeftOuter),
#"Expanded Rushing" = Table.ExpandTableColumn(#"Merged Queries1", "Rushing", {"Rush-Gms", "Rush-Att", "Rush-Yds", "Rush-Avg", "Rush-YPG", "Rush-Lg", "Rush-TD", "Rush-FD"}, {"Rush-Gms", "Rush-Att", "Rush-Yds", "Rush-Avg", "Rush-YPG", "Rush-Lg", "Rush-TD", "Rush-FD"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Rushing",{"SeasonPlayerTeamKey"},Receiving,{"SeasonPlayerTeamKey"},"Receiving",JoinKind.LeftOuter),
#"Expanded Receiving" = Table.ExpandTableColumn(#"Merged Queries2", "Receiving", {"Receiving-Gms", "Receiving-Att", "Receiving-Yds", "Receiving-Avg", "Receiving-YPG", "Receiving-Lg", "Receiving-TD", "Receiving-FD"}, {"Receiving-Gms", "Receiving-Att", "Receiving-Yds", "Receiving-Avg", "Receiving-YPG", "Receiving-Lg", "Receiving-TD", "Receiving-FD"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Receiving","STL","LA",Replacer.ReplaceText,{"Team"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","SD","LAC",Replacer.ReplaceText,{"Team"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","t","",Replacer.ReplaceText,{"Receiving-Lg"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","t","",Replacer.ReplaceText,{"Pass-Lg"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","t","",Replacer.ReplaceText,{"Rush-Lg"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value4",{{"Season", Int64.Type}, {"PlayerName", type text}, {"PlayerShortName", type text}, {"PlayerLastName", type text}, {"Team", type text}, {"SeasonPlayerTeamKey", type text}, {"PlayerURL", type text}, {"Position", type text}, {"PlayerImgURL", type text}, {"Pass-Att", Int64.Type}, {"Pass-Cmp", Int64.Type}, {"Pass-Pct", type number}, {"Pass-Yds", Int64.Type}, {"Pass-YPA", Int64.Type}, {"Pass-TD", Int64.Type}, {"Pass-TD%", Int64.Type}, {"Pass-Int", Int64.Type}, {"Pass-Int%", Int64.Type}, {"Pass-Lg", Int64.Type}, {"Pass-Sack", Int64.Type}, {"Pass-Loss", Int64.Type}, {"Pass-Rate", Int64.Type}, {"Rush-Gms", Int64.Type}, {"Rush-Att", Int64.Type}, {"Rush-Yds", Int64.Type}, {"Rush-Avg", type number}, {"Rush-YPG", type number}, {"Rush-Lg", Int64.Type}, {"Rush-TD", Int64.Type}, {"Rush-FD", Int64.Type}, {"Receiving-Gms", Int64.Type}, {"Receiving-Att", Int64.Type}, {"Receiving-Yds", Int64.Type}, {"Receiving-Avg", type number}, {"Receiving-YPG", type number}, {"Receiving-Lg", Int64.Type}, {"Receiving-TD", Int64.Type}, {"Receiving-FD", Int64.Type}})
in
#"Changed Type"
1 Reply
- v-yulgu-msft
Microsoft Employee
Hi Anonymous,
Please provide some sample data so that I can test on my side.
Regards,
Yuliana Gu