cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aktripathi2506 Helper IV
Helper IV

data 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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
KGrice Solution Sage
Solution Sage

Re: data 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:

 

Input.PNG

 

 

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.

View solution in original post

Microsoft AlexChen
Microsoft

Re: data type which contains both test and number

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]

 

5.png

 

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.png

 

 

 

View solution in original post

6 REPLIES 6
KGrice Solution Sage
Solution Sage

Re: data type which contains both test and number

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
aktripathi2506 Helper IV
Helper IV

Re: data type which contains both test and number

Thank 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 Solution Sage
Solution Sage

Re: data 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:

 

Input.PNG

 

 

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.

View solution in original post

aktripathi2506 Helper IV
Helper IV

Re: data type which contains both test and number

Thank 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 AlexChen
Microsoft

Re: data type which contains both test and number

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]

 

5.png

 

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.png

 

 

 

View solution in original post

ravsha85 Helper IV
Helper IV

Re: data type which contains both test and number

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,

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors