Expression.Error: There weren't enough elements in the enumeration to complete the operation.

[Anonymous]

Hi - I am using a Grouped Rows function to produce two columns: Total Fees and Total AUM. Total AUM works, but Total Fees does not. 

I keep getting the error:

Expression.Error: There weren't enough elements in the enumeration to complete the operation. Details: [List]

When I click the error, it takes me back to the Grouped Rows section of my code.

Below is my code. Any idea what I could be tripping up on? To my newb eyes, the set of code that creates Total AUM is the same in structure as the one that creates Total Fees, but clearly I'm overlooking something. Happy to post the entirety of the code if that helps (all the other rows). 

#"Grouped Rows" = Table.Group(#"Changed Type2", {"Value", "QtrEndDate"},{

//Create a column called Total AUM - this section of code works great
{"Total AUM", (t)=>
let
#"LeadAdvisor" = Table.SelectRows(t,each [Attribute] = "LeadAdvisor"),
#"LeadAdvisor AUM" = List.Sum(#"LeadAdvisor"[AUM]),
#"Remove LeadAdvisor Clients" = Table.SelectRows(t, each not List.Contains(#"LeadAdvisor"[Client],[Client])),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Remove LeadAdvisor Clients",
{"Client", "AUM", "Attribute","QtrEndDate", "Value"}, "Attribute.1", "Value.1"),

//Match Sales to Sales%
#"Sales Attrib" = Table.TransformColumns(#"Unpivoted Columns1",{
//Looks like we are splitting off just down to the # in Sales1, Sales2, Sales3
{"Attribute", each Text.Split(_,"s"){1}},
{"Attribute.1", each Text.SplitAny(_,"s%"){1}}}),
AUMs = Table.SelectRows(#"Sales Attrib", each [Attribute] = [Attribute.1]),
#"Sales AUM" = List.Sum
(List.Generate(
()=>[f=AUMs[AUM]{0} * AUMs[Value.1]{0}, idx=0],
each [idx] < Table.RowCount(AUMs),
each [f=AUMs[AUM]{[idx]+1} * AUMs[Value.1]{[idx]+1}, idx=[idx]+1],
each [f]
))
in
#"LeadAdvisor AUM" + #"Sales AUM", Currency.Type
},

//Create a second column called Total Fees
{"Total Fees", (t)=>
let
//return a table where the Attribute = "Lead Advisor" - essentially filtering the table
#"LeadAdvisor2" = Table.SelectRows(t,each [Attribute] = "LeadAdvisor"),
//add up values from the newly-created table
#"LeadAdvisor Fees" = List.Sum(#"LeadAdvisor2"[Fees paid]),
//return a table where ????
#"Remove LeadAdvisor Clients2" = Table.SelectRows(t, each not List.Contains(#"LeadAdvisor2"[Client],[Client])),
#"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Remove LeadAdvisor Clients2",
{"Client", "Fees paid", "Attribute","QtrEndDate", "Value"}, "Attribute.2", "Value.2"),

//Match Sales to Sales% - below is the section that is causing the error, b/c when I remove it, I get incorrect values instead of errors
#"Sales Attrib2" = Table.TransformColumns(#"Unpivoted Columns2",{
//Splitting Sales1, Sales2, Sales3 into just numbers
{"Attribute.2", each Text.SplitAny(_,"s%"){1}}}),
//Return rows where Attribute (role) =???
Fees = Table.SelectRows(#"Sales Attrib2", each [Attribute] = [Attribute.2]),
#"Sales Fees" = List.Sum
(List.Generate(
()=>[f=Fees[Fees paid]{0} * Fees[Value.2]{0}, idx=0],
each [idx] < Table.RowCount(Fees),
each [f=Fees[Fees paid]{[idx]+1} * Fees[Value.2]{[idx]+1}, idx=[idx]+1],
each [f]
))
in
#"LeadAdvisor Fees"+ #"Sales Fees" , Currency.Type
}
})
in
#"Grouped Rows"

[HotChilli]

Not easy to debug this.

What it boiled down to is the number of words in "AUM" v "Fees paid".

This part:

{"Attribute.2", each Text.SplitAny(_,"s%"){1}}})

is going to split the column on charcters in "s%" and return the 2nd portion .

 This works fine for the "Sales1%" values but breaks because the first line in the mini-table has a value of "AUM" in attribute.2 so there's nothing for the {1} to refer to. So you get an enumeration error.

Now it looks like you don't need the AUM line in the stage at all so removing it might solve your problem.

May I suggest that you aid your debugging by having the code from the function in a separate query.  You can debug it step-by-step there.

[Anonymous]

Interesting, okay. I really appreciate you taking a look - I will play around with your suggestions and see if I can't figure it out. It helps knowing where I should be looking, so thank you!

[Anonymous]

See below. The basic gist of this is that each client has a LeadAdvisor, and they also can have a Sales1 person, a Sales2 person, and/or a Sales3 person. Sometimes a LeadAdvisor also shows up in a Sales spot. Each of those sales positions is assigned a percentage. 

If a Value is in the Lead Advisor spot, then we want to sum 1*AUM and 1*Fees Paid (so the full amount of each).

If a Value is in any of the Sales1,2,3 spots, and is not also LeadAdvisor then we want AUM and Fees times the respective Sales%, and we don't want to double count clients. 

But if a Value is in both LeadAdvisor and a Sales spot, they only get the 1*AUM and 1*Fees.

So if Kevin is both Lead and Sales2, he just gets 1*AUM and 1*Fees on that client, he does not also get Sales2%*AUM and Sales2*Fees. But if Kevin is only in a Sales spot for a client and not a Lead, then he only gets the %*Sales. 

Thank you! 

QtrEndDate	Client	Attribute	AUM	Fees paid	Sales1%	Sales2%	Sales3%	Value
12/31/2022	0261C7E9-2579	LeadAdvisor	$         8,006,181	$      57,679	1	0	0	Eric
12/31/2022	0261C7E9-2579	Sales1	$         8,006,181	$      57,679	1	0	0	Howard
12/31/2022	12579E87-46DA	Sales1	$         3,497,261	$      17,148	0.5	0.5	0	Craig
12/31/2022	257F23A2-ED44	LeadAdvisor	$              96,806	$            902	1	0	0	Jay
12/31/2022	257F23A2-ED44	Sales1	$              96,806	$            902	1	0	0	Jay
12/31/2022	257F9CA9-444D	LeadAdvisor	$       32,601,966	$      113,302	0.85	0.12	0.03	Tim
12/31/2022	257F9CA9-444D	Sales2	$       32,601,966	$      113,302	0.85	0.12	0.03	Tim
12/31/2022	257F9CA9-444D	Sales3	$       32,601,966	$      113,302	0.85	0.12	0.03	Chris
12/31/2022	49EAAB00-257E	LeadAdvisor	$                       -	$               -	1	0	0	Amanda
12/31/2022	49EAAB00-257E	Sales1	$                       -	$               -	1	0	0	Amanda
12/31/2022	52325704-1372	LeadAdvisor	$            467,760	$         7,328	0.75	0.25	0	Amanda
12/31/2022	52325704-1372	Sales1	$            467,760	$         7,328	0.75	0.25	0	Amanda
12/31/2022	5A782577-41E7	LeadAdvisor	$         1,862,783	$               -	0.25	0.5	0.25	Mike
12/31/2022	5A782577-41E7	Sales2	$         1,862,783	$               -	0.25	0.5	0.25	Mike
12/31/2022	5A782577-41E7	Sales3	$         1,862,783	$               -	0.25	0.5	0.25	Gail
12/31/2022	84257562-FF46	LeadAdvisor	$         12,029,180	$      69,000	1	0	0	Ted
12/31/2022	84257562-FF46	Sales1	$         12,029,180	$      69,000	1	0	0	Ted
12/31/2022	B825795F-69B8	LeadAdvisor	$         1,388,739	$         9,719	1	0	0	Jay
12/31/2022	B825795F-69B8	Sales1	$         1,388,739	$         9,719	1	0	0	Jay
12/31/2022	D257B373-76FB	LeadAdvisor	$            147,278	$         2,256	0.75	0.25	0	Amanda
12/31/2022	D257B373-76FB	Sales1	$            147,278	$         2,256	0.75	0.25	0	Amanda
12/31/2022	EB5DC257-7367	LeadAdvisor	$                       -	$              67	1	0	0	Craig
12/31/2022	EB5DC257-7367	Sales1	$                       -	$              67	1	0	0	Craig
12/31/2022	F257EAB4-1387	LeadAdvisor	$            278,195	$         2,661	1	0	0	Grant
12/31/2022	F257EAB4-1387	Sales1	$            278,195	$         2,661	1	0	0	Grant
12/31/2022	FE825739-847D	LeadAdvisor	$            877,339	$         6,908	0.5	0.5	0	Kevin
12/31/2022	FE825739-847D	Sales1	$            877,339	$         6,908	0.5	0.5	0	Kevin

[ams1]

Hi,

Any chance you can give us some sample data for the #"Changed Type2" step so that I can easily run your code on my machine?

Ideally in a way that I can I can paste in Power Query, but a table screenshot will do also.