Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Hi @I_Like_Pi,
Can you please share a pbix file for test?
Regards,
Xiaoxin Sheng
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |