Forum Discussion
Helper IVdata type which contains both test and number
Hi,
Just curious to know if there is a way to store both number and text in one column.
Actually I want to apply a formula something like this:
if(number), return some value, ""
So if in one column I have both text and number than I can apply isnumber furmula and get the result.
However if I convert entire column in text OR number than I can not use this formula to get the desired result.
Note: I saw one of my column is having "any" data type when I see in transform tab in query editor mode but I dont get this option as a drop down.
Thanks.
From your output column, it looks like you want anything that is only a number, or anything that contains CAD. To get that, I added a custom column with the formula:
=try Number.From([Input Values]) otherwise if Text.Contains([Input Values], "CAD") then [Input Values] else ""
The result:
You could then filter the Output column to exclude blanks, then delete the column, and you'd be left with only the values you want.
Hi aktripathi2506,
1. If you want to select completely opposite, you can use code below:
if Value.Is([value], type number) then "" else if Text.Contains([value], "CAD") then "" else [value]
2. If you want to add more text, such as IND, US, you can write like below:
try Number.From([value]) otherwise
if Text.Contains([value], "CAD") then
[value]
else if Text.Contains([value], "IND") then
[value]
else if Text.Contains([value], "US") then
[value]
else
""
6 Replies
KGriceMemorable Member
The Any data type is just when Power BI can't figure out what your data type should be, but you can't intentionally pick it. A column can only set as one Data Type.
For the formula you're looking to do, what value are you wanting to use? You won't end up with text and numbers in the same column, unless the numbers are actually text. But you can do a test to get the numbers out and use a supplied number otherwise, or something else if needed. The try statement is
=try Number.From([NumberTextColumn]) otherwise 0
aktripathi2506Thank you KGrice for reply.
If I convert entire column as text then is there a way to get only records which start with a certain text like CAD or which have only numbers.
Basically I want only that data which contain specific text (for example CAD in this case) or which have only number.
if data is
CAD
CAD2011
2010CAD
CAD-2016
9999
ERY
Test
2000-Test
199-EW
My expected output is:
CAD
CAD2011
2010CAD
CAD-2016
9999
Please advise, Thanks.
- KGrice
From your output column, it looks like you want anything that is only a number, or anything that contains CAD. To get that, I added a custom column with the formula:
=try Number.From([Input Values]) otherwise if Text.Contains([Input Values], "CAD") then [Input Values] else ""
The result:
You could then filter the Output column to exclude blanks, then delete the column, and you'd be left with only the values you want.