cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jpt1228 Member
Member

Best practice to deal with column mixed value/text

Hello, I am working on a new data model and I have a column that has text, whole numbers, and decimal numbers. I need to create calculations with the numeric values in this column but can't convert column type to value due to text in column - Which I may need the text.

 

Should I pivot the column into seperate columns by value type or create 3 different tables - Each filtered on the data type: Table1 then reference Table1 and filter column for text rename table Table1text, Reference Table1 and filter column for whole number and rename Table1wholenum for each data type?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Best practice to deal with column mixed value/text

I would just add a new column in the one table that will be used for calculations. 

try
Number.From([Value])
otherwise
null

PQ new Column for want numbers and null for text.png

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Best practice to deal with column mixed value/text

I would just add a new column in the one table that will be used for calculations. 

try
Number.From([Value])
otherwise
null

PQ new Column for want numbers and null for text.png

View solution in original post

jpt1228 Member
Member

Re: Best practice to deal with column mixed value/text

Never thought of that. Great simple solution @Nick_M 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)