My Query seems to always return a Function or an Error

[kmcardle]

First, thank you all for the support you provide on this forum.  It's been a very great experience for a new Power BI developer like me.

I'm currently trying to add some custom columns to my data source.  I have figured out how to do most of the column additions but I am now trying to add a column that counts the number of records with the same record identifer ("case number").  I was able to add this as a calculated column in Power BI but when I try to do this in Power Query (custom Column), it always returns either a function in the records new column or if I change a few things it returns an error.  I've added an index to allow me to compare the "case number" of the previous row to the current row "case number"

Here is my code:
= Table.AddColumn(#"Changed Type8", "Cnt Same Case", each let
casecnt = 1,
_casecnt = try(each if(#"Changed Type8"[Index] = 0) then casecnt
else if(#"Changed Type8"[CaseNum]{[Index]} = #"Changed Type8"[CaseNum]{[Index]-1}) then #"Changed Type8"[Cnt Same Case]{[Index]-1} + 1 else casecnt) otherwise 1
in
(_casecnt))

This particular code returns a 'function' for each row's new field "Cnt Same Case".

I'm pretty sure my code should return a numberic value of either '1' or higher for each row in the data source but instead is returning a function. If I click on the function hyperlink in a column, it pops up a window asking for a parameter.

I would sure appreciate any help you can offer.   Thank you so much.

[PhilipTreacy · Accepted solution]

Hi @kmcardle 

Download my example PBIX file

Do you want the result to look like this:

If so then you can create a new column with this code

let _id = [ID] in List.Count(List.Select(#"Changed Type"[ID], each _ = _id))

Regards

Phil

[PhilipTreacy]

Hi @kmcardle 

If you are trying to count the number of records with the same identifier, you could do that by Grouping the identifier and setting the Aggregation to Count, for example:

If that doesn't suit your needs, please post some sample data and an example of the result you are looking for.

Regards

Phil

[kmcardle]

Thank you Phil.  How then would I populate a new column with the count?  BTW I really appreciate that you provided such a great answer.  Thank you. 

[PhilipTreacy · Accepted solution]

Hi @kmcardle 

Download my example PBIX file

Do you want the result to look like this:

If so then you can create a new column with this code

let _id = [ID] in List.Count(List.Select(#"Changed Type"[ID], each _ = _id))

Regards

Phil

[kmcardle · Accepted solution]

Wow. Exactly what the doctor ordered.  Thank you!

Kevin