Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm trying to create a list of the number of times each item appears in a list. For example, {"A", "B", "B", "C", "C", "C"} would yield either {1,2,3} or {1, 2, 2, 3, 3, 3}. I plan to do a List.Max() of the result, so both of those would give same result. I am pretty close and can get it to work when the search list is fixed, as below:
[ListA] is the column of Lists, one row of which is {"A", "B", "B", "C", "C", "C"}
= Table.AddColumn(#"Renamed Columns", "List of Counts Fixed", each List.Transform([ListA], each List.Count(List.FindText({"A", "B", "B", "C", "C", "C"}, _))))
the above results in {1, 2, 2, 3, 3, 3}
But if I use a column reference as the search list. I get a list of "Error"s. I get same if I reference the original list or if i first duplicate the column and reference a "different" column of lists.
= Table.AddColumn(#"Added Custom2", "List of Counts", each List.Transform([ListA], each List.Count(List.FindText([ListA],_))))
How do I properly reference the list on the current row of the table in the above code? Is there a much simpler way to go I am missing?
Solved! Go to Solution.
Well, I figured it out on my own, but here is solution in case anyone is curioius. I needed to store the list in a variable after the initial "each".
= Table.AddColumn(#"Added Custom2", "Custom", each let templist=[ListA] in List.Transform([ListA], each List.Count(List.FindText(templist, _))))
Comments still welcomed on an easier way to do same.
Well, I figured it out on my own, but here is solution in case anyone is curioius. I needed to store the list in a variable after the initial "each".
= Table.AddColumn(#"Added Custom2", "Custom", each let templist=[ListA] in List.Transform([ListA], each List.Count(List.FindText(templist, _))))
Comments still welcomed on an easier way to do same.
@mahoney,
Glad to hear the issue is solved, you can accept your solution as anwser, other community members would benefit from this solution when they get same issues.
Regards,
Lydia
User | Count |
---|---|
102 | |
91 | |
87 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |