Forum Discussion
Helper IVdata type which contains both test and number
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
""
Helper IVThank you KGrice, This was helpful.
I have 2 questions now:
1. What is the way to add more text like "CAD" ( I tried adding OR function but did not work). For example I want to look for text which have CAD, IND and US.
2. What is the way to select completely opposite; means select all except the text which contains CAD, IND, US-12.
Thank you so much for your time and efforts.
Microsoft EmployeeHi 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
""
Hi AlexChen,
I have a similar case, but I want only the numeric values or the text values from a column which has both numeric and text values.
In this case how can I use DAX or power query to acheive this?
Regards,