Group-by (Calculated column), Expression.Error: the 'offset' argument is out of range.

[I_Like_Pi]

I have a Data set that is missing a unique joining field. In order to create the field I built a calculated column that aggregates a field with the last 4 digits of a second field. (i.e.   UniRec = Text.Trim([Name] & Text.Range([Redacted],12,4)

Redacted would be unique but we purposely XXXX the first 12 values like you see in a credit card (coincidently 16 characters). 

The column looks right... but when I use it in a group by I get the Expression.Error above.

I am playing with it as I post and just noticed that the Data type at the top is ABC/123 I converted it to text, and now it works. So if you find this post, I just learned that the data type matters for group by.

What I dont know is why. If anyone out there understands why it matters. Please enlighten me or I am destined to repeat.

[Anonymous]

Hi @I_Like_Pi,

Can you please share a pbix file for test?

Regards,

Xiaoxin Sheng

[I_Like_Pi · Accepted solution]

Unfortunately I cannot post the associated file. But I am more interested in understanding if there are datatype limits on the "grouping" columns. Reading some other posts I now know that you should really specify any of the columns that PQ leaves undefined (i.e. ABC 123) or subsequent actions may treat it as text or a number inconsistently depending upon the data that shows up. As is the case here (the column datatype was not defined).

The error seems vague to me I wondered if there were other circumstances (data types) where a Grouping step would throw such an error.

Regardless, thanks for you interest Xiaoxin.

John