Forum Discussion
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
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:
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"
4 Replies
- ams1Responsive Resident
Hi,
Any chance you can give us some sample data
for the#"Changed Type2" stepso 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.
- AnonymousNot applicable
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 - HotChilliCommunity Champion
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.
- AnonymousNot applicable
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!