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.
Hi Folks
I need to search a column and determine if the contents contain just Text or just Numbers . The dataset I am searching is largish (>5 M rows). In Excel, the ISNUMBER and ISTEXT functions work at the cell level and produce what I am after (see screenshot below). DAX calculations in PowerBI appear to work differently- they appear to be working on the datatype of the column (also in screenshot). I can't see a simple/ elegant PowerQuery solution. I am pulling data from a SQL Server DB, so contemplating pulling the data from a SQL View that contains Regex that does the search, or perhaps just taking the "WHERE ...."LIKE" route with wildcards.
In terms of processing power- I have a Virtcual Machine with PowerBI running with RAM 8 (althjough I could dial this up to 16 GB), and the SQL DB has 64 GB.
Can anyone suggest the best approach/ pros vs cons, considering the dataset size?
Cheers
Steve
Here is a link to my PowerBI file
Solved! Go to Solution.
@stfox Yes in Excel cells can have their own data types while in PBI it's the whole column!
However in both Excel and PBI you can tell the data type by just looking for this:
TEXT - left justified
NUMBER - right justified
Which you can obvioualy confirm by looking at your pictures! The only strange thing is cell A2 in Excel?
EDIT: I guess even specifically formatted as TEXT - the 55 (cell A2) is recognized in Excel as Number.
Anyway what you can do is test if the value can be converted to a number...
Can Convert = NOT ( ISERROR (Sheet1[Item to Test] + 0 ) )
Hope this helps!
@stfox Yes in Excel cells can have their own data types while in PBI it's the whole column!
However in both Excel and PBI you can tell the data type by just looking for this:
TEXT - left justified
NUMBER - right justified
Which you can obvioualy confirm by looking at your pictures! The only strange thing is cell A2 in Excel?
EDIT: I guess even specifically formatted as TEXT - the 55 (cell A2) is recognized in Excel as Number.
Anyway what you can do is test if the value can be converted to a number...
Can Convert = NOT ( ISERROR (Sheet1[Item to Test] + 0 ) )
Hope this helps!
Thanks Sean
So essentially we check if an error is thrown when an addition (+0) is attempted on the contents of the column.
Cheers
Steve
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |